ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效

Question

ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP、OFFSET 或 FOR XML。

我在尝试执行以下查询时收到上述错误。任何人都可以看看并告诉我我在这里做错了什么吗？

 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

原文由 Kamran Ahmed 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您不需要在 WHERE 子句之后的内部查询中使用 ORDER BY ，因为您已经在 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

原文由 Himanshu 发布，翻译遵循 CC BY-SA 3.0 许可协议