# ORDER BY子句在视图、内联函数、派生表、子查询和普通表表达式中是无效的[英] The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

### 问题描述

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

## 推荐答案

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

## 其他推荐答案

```ORDER BY column OFFSET 0 ROWS
```

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

### 问题描述

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

## 其他推荐答案

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