The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
This is an error and No, you can't use ORDER BY to sort rows in the derived table for the reason given in the error message. The order of rows in the result set is ultimately controlled by an ORDER BY clause in the outer SELECT, not by the OVER clause. The OVER clause &quot;specifies the logical order in which the window functioncalculation is performed&quot;. It doesn't sort the result set. There are workarounds for this issue though and you can check solution proposed at https://stackoverflow.com/questions/18031421/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu either by using ROW_NUMBER function or ORDER BY column OFFSET 0 ROWS.
Alternative method is explained by Mr. Pinal Dave at this link.
Hope this helps.