The ORDER BY clause is not valid in views, built-in functions, views, subqueries, and common table expressions

Question

The ORDER BY clause is not valid in views, built-in functions, retrieved tables, subqueries, and general table expressions except TOP, OFFSET, or FOR XML.

I get the above error while trying to execute the following query. Can someone please take a look and tell me what I'm doing wrong here?

SELECT * FROM ( SELECT Stockmain.VRNOA, item.description as item_description, party.name as party_name, stockmain.vrdate, stockdetail.qty, stockdetail.rate, stockdetail.amount, ROW_NUMBER() OVER (ORDER BY VRDATE) AS RowNum FROM StockMain INNER JOIN StockDetail ON StockMain.stid = StockDetail.stid INNER JOIN party ON party.party_id = stockmain.party_id INNER JOIN item ON item.item_id = stockdetail.item_id WHERE stockmain.etype='purchase' ORDER BY VRDATE DESC ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN 1 and 5 

Answer 1

You do not need to use ORDER BY in the inner query after the WHERE because you have already used it in ROW_NUMBER() OVER (ORDER BY VRDATE DESC) .

 SELECT * FROM ( SELECT Stockmain.VRNOA, item.description as item_description, party.name as party_name, stockmain.vrdate, stockdetail.qty, stockdetail.rate, stockdetail.amount, ROW_NUMBER() OVER (ORDER BY VRDATE DESC) AS RowNum --< ORDER BY FROM StockMain INNER JOIN StockDetail ON StockMain.stid = StockDetail.stid INNER JOIN party ON party.party_id = stockmain.party_id INNER JOIN item ON item.item_id = stockdetail.item_id WHERE stockmain.etype='purchase' ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN 1 and 5