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
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