The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
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 MyDerivedTableWHERE MyDerivedTable.RowNum BETWEEN 1 and 5
ORDER BY column OFFSET 0 ROWS
Surprisingly makes it work, what a strange feature.
A bigger example with a CTE as a way to temporarily "store" a long query to re-order it later:
;WITH cte AS ( SELECT .....long select statement here....)SELECT * FROM ( SELECT * FROM ( -- necessary to nest selects for union to work with where & order clauses SELECT * FROM cte WHERE cte.MainCol= 1 ORDER BY cte.ColX asc OFFSET 0 ROWS ) first UNION ALL SELECT * FROM ( SELECT * FROM cte WHERE cte.MainCol = 0 ORDER BY cte.ColY desc OFFSET 0 ROWS ) last) as unionizedORDER BY unionized.MainCol desc -- all rows ordered by this oneOFFSET @pPageSize * @pPageOffset ROWS -- params from stored procedure for pagination, not relevant to exampleFETCH FIRST @pPageSize ROWS ONLY -- params from stored procedure for pagination, not relevant to example
So we get all results ordered by MainCol
But the results with MainCol = 1
get ordered by ColX
And the results with MainCol = 0
get ordered by ColY