The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
SqlSql ServerDatabaseSql Server-2012Sql Query-StoreSql Problem Overview
> 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
Sql Solutions
Solution 1 - Sql
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 - Sql
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
Solution 3 - Sql
You can do it like this way(here i have only given a sort query)
SELECT * FROM
(SELECT TOP 200 * FROM YourTableName ORDER BY Created_Date DESC) as myAlias
ORDER BY Created_Date desc