sql server - The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expression

Question

This question already has an answer here: question from:https://stackoverflow.com/questions/65911476/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu

Answer 1

As it states - you cannot include an ORDER BY clause in the definition of a view. It is in much the same way that the order of data returned from a table when no ORDER BY is applied on the SELECT means that the order of data is not guaranteed.

Apply the ORDER BY in the queries that SELECT from the view instead.

Instead of

ALTER VIEW [dbo].[vw_Test_booking]  
AS  
 select NEWID() as UniqueKey, * 
 from (
    SELECT  [startdatetime]           AS StartDateTime,    
           [bookingdatetime]         AS BookingDateTime, 
           [firstname]               AS firstname       
    FROM   [dbo].[vw_Testpatient] 
    WHERE  statusCode in (                       
    '2',    
    '0      
 )       
) T
ORDER BY startdatetime DESC

Apply it like

SELECT *
FROM dbo.vw_Test_booking
ORDER BY StartDateTime  DESC