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