CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012sql-query-store
Kamran Ahmed
Kamran Ahmed

Reputation: 12428

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

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

Upvotes: 133

Views: 323569

Answers (4)

Altamash Bin Tariq
Altamash Bin Tariq

Reputation: 61

Adding a select top xyz worked in my case.

Upvotes: 0

Ashish Tripathi
Ashish Tripathi

Reputation: 666

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,

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

Upvotes: 0

Jose V
Jose V

Reputation: 1854

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

Upvotes: 76

Himanshu
Himanshu

Reputation: 32602

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

Upvotes: 119

Related Questions