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   

Solution 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 

Solution 2

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 unionized
ORDER BY unionized.MainCol desc -- all rows ordered by this one
OFFSET @pPageSize * @pPageOffset ROWS -- params from stored procedure for pagination, not relevant to example
FETCH 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

Comments

[Mike Sherrill 'Cat Recall']

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 "specifies the logical order in which the window functioncalculation is performed". It doesn't sort the result set.

[Black]

Can anyone offer a glimpse of insight as to why Microsoft would require this absurd redundant additional syntax?

[J.M. Janzen]

Well I'll be darned, it just works. I hate that I have to do it, but I love that the solution is so easy. Your snippet at the very top worked out-of-the-box for my query.