The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

Question

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

I am getting the above said error while trying to execute the following query. Can anyone please have a look and tell me what am I 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 inner query after WHERE clause 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