Insert duplicate row in SQL Server view

Ask Time：2018-07-10T14:29:11 Author：lordvlad30

I was wondering if it was possible in SQL Server 2008 R2 to create a view with only the last column (DateTime DESC), but this last row should be copied in the view again.

So the end result would be a View with two rows with the same data.

The query to select one row is easy:

SELECT TOP 1 * 
FROM Reporting
ORDER BY DateTime DESC

SELECT TOP 1 *
FROM Reporting
WHERE DateTime IN (SELECT MAX(DateTime) 
                   FROM Reporting)

This returns only one row, but I want to duplicate this row in the view again.

Thanks

Author:lordvlad30，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/51258514/insert-duplicate-row-in-sql-server-view

gotqn :

The syntax in the above answer is invalid. You are not allowed to have ORDER BY in each data source in the UNION ALL. You can have only one at the final statement. So, this is wrong:\n\nSELECT TOP 1 * FROM Reporting ORDER BY DateTime DESC \nUNION ALL\nSELECT TOP 1 * FROM Reporting ORDER BY DateTime DESC \n\n\nAnd should be done like this:\n\nSELECT * FROM (SELECT TOP 1 * FROM Reporting ORDER BY DateTime DESC)\nUNION ALL\nSELECT * FROM (SELECT TOP 1 * FROM Reporting ORDER BY DateTime DESC);\n\n\nI will advice using a different approach. Use fake data source and then cross apply.\n\nSELECT SI.*\nFROM\n(\n SELECT 1\n UNION ALL\n SELECT 2\n) DS ([col])\nCROSS APPLY\n(\n\n SELECT TOP 1 * FROM Reporting ORDER BY DateTime DESC\n) SI;\n\n\nYou can test easily that the execution plan of this statement is better causing only one ordering and index scan:\n\n",

2018-07-10T06:44:10

Insert duplicate row in SQL Server view