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

the order by should be outside the subquery select as the error said — Daniele, Aug 03 '13 at 09:41

score 116 · Accepted Answer · answered Aug 03 '13 at 09:41

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

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](http://technet.microsoft.com/en-us/library/ms189461.aspx) "specifies the *logical* order in which the window functioncalculation is performed". It doesn't sort the result set. — Mike Sherrill 'Cat Recall', Aug 03 '13 at 12:19

Jose V · Answer 2 · 2020-02-14T22:37:15.037

66

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

edited Feb 14 '20 at 22:37

answered Feb 14 '20 at 19:39

Jose V

1,655
1
17
31

16

Can anyone offer a glimpse of insight as to why Microsoft would require this absurd redundant additional syntax? – Black Jun 09 '20 at 10:45
3

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. – J.M. Janzen Jan 15 '21 at 17:02
With of course the usual caveat that this "works" right up until the optimizer is made smart enough to optimize it out again. The purpose of allowing order by to be used anywhere other than in the outermost query is to make *other* features such as `OFFSET` or `TOP` be well defined. But when you use that feature and say "give me *all* of the rows" then it's misuse. That it currently seems to also result in sorted output is programming by *coincidence* not by contract. – Damien_The_Unbeliever Sep 07 '22 at 06:54
@Damien_The_Unbeliever Misuse according to which manual? – Jose V Sep 07 '22 at 21:58
How about [SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16#limitations-and-restrictions): "When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results" – Damien_The_Unbeliever Sep 08 '22 at 05:55
Or you could review the multitude of questions that got asked here when the similar `TOP 100 PERCENT` "trick" stopped working. I.e. the optimizer has been improved before to know there's no need to perform sorting when you're asking for all rows, there's no reason to believe it'll continue being stupid about `OFFSET 0 ROWS`. – Damien_The_Unbeliever Sep 08 '22 at 06:06
@Damien_The_Unbeliever the manual does say that, what a weird thing random limitation they decided upon. Kind of strange to say that deprecating this desired behavior is an "optimization", or the smart thing to do. – Jose V Sep 08 '22 at 18:51
It's not a "random limitation", it's that it doesn't make *sense*. In SQL you describe the *results you want*, not a *prescriptive model of how to obtain them*. That you don't provide to the system an order by clause on the outer query means that you *do not care about* the order of results. That an earlier part of your query did require a specific ordering to be well defined is irrelevant – Damien_The_Unbeliever Sep 08 '22 at 19:13
@Damien_The_Unbeliever I did provide an `order by` clause on the outer query though, I simply require the sub queries to *also* be `order by`. I am describing the results I want. – Jose V Sep 09 '22 at 21:18

score 0 · Answer 3 · answered Sep 07 '22 at 06:42

In my case, I was using an "Inline Table-Valued User Defined Function" and trying to do an order by. And I was getting below exception:

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.

Solution I changed it to Multiline Statement Table-Values Function and it started working.

To do so instead of returning the data straightaway from the SQL,

I defined a table variable and columns
In my UDF body filled the data in it
Return

score 0 · Answer 4 · answered Mar 13 '23 at 14:16

0

Adding a select top xyz worked in my case.

answered Mar 13 '23 at 14:16

Altamash Bin Tariq

17
2

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

4 Answers4

Linked