SQL ORDER BY ROW_NUMBER

ORDER BY ROW_NUMBER

提问by Alecs

UPD: thanks for all, topic closed, after sleeping I understand everything =)

UPD：谢谢大家，话题结束，睡觉后我明白了一切=）

I have a problem with understanding OVER clause and and ROW_NUMBER function. Simple table - name and mark. I want to calculate average mark for each name.

我在理解 OVER 子句和 ROW_NUMBER 函数时遇到问题。简单的表 - 名称和标记。我想计算每个名字的平均分。

SELECT top 1 with ties name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM table 
ORDER BY AVG(mark) OVER(PARTITION BY name)

it will display something like this, and I understand why - that is what ROW_NUMBER() does

它会显示这样的东西，我明白为什么 - 这就是 ROW_NUMBER() 所做的

name|number
Pete 1
Pete 2

But if I write

但是如果我写

SELECT top 1 with ties name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM table 
ORDER BY AVG(mark) OVER(PARTITION BY name), number

it will display

它会显示

name|number
Pete 1

And this time I don't understand how ORDER BY works with ROW_NUMBER() function. Can somebody explain it to me?

这一次我不明白 ORDER BY 如何与 ROW_NUMBER() 函数一起工作。有人可以向我解释一下吗？

回答by beach

You can certainly order by ROW_NUMBER columnbecause the SELECT clause is evaluated before the ORDER BY clause. You can ORDER BY any columnor column alias. This is why no error message was thrown (because it is valid).

您当然可以按 ROW_NUMBER 列排序，因为 SELECT 子句在 ORDER BY 子句之前进行评估。您可以 ORDER BY 任何列或列别名。这就是为什么没有抛出错误消息的原因（因为它是有效的）。

SELECT name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM @table 
ORDER BY number

Evaluates to

评估为

name       number
---------- --------------------
John       1
pete       1
pete       2
John       2
pete       3

OP's second example of row_number is not correct.

OP 的第二个 row_number 示例不正确。

SELECT AVG(mark) OVER(PARTITION BY name), name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM @table 
ORDER BY AVG(mark) OVER(PARTITION BY name), number

Returns as expected because AVG is the first sort column followed by number.

按预期返回，因为 AVG 是第一个排序列，后跟数字。

            name       number
----------- ---------- --------------------
11          pete       1
11          pete       2
11          pete       3
17          John       1
17          John       2

Change the query to number DESCand pete is still first however the row numbers are descending order.

将查询更改为数字 DESC并且 pete 仍然是第一个，但行号是降序排列的。

            name       number
----------- ---------- --------------------
11          pete       3
11          pete       2
11          pete       1
17          John       2
17          John       1

SQL Order of operations

SQL 操作顺序

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

回答by gbn

You can't ORDER BY the ROW_NUMBER directly: I don't know why you didn't get an error on this case, but normally you would. Hence the use of derived tables or CTEs

您不能直接按 ROW_NUMBER ORDER：我不知道为什么在这种情况下您没有收到错误消息，但通常情况下您会。因此使用派生表或 CTE

SELECT
    name, number
FROM
    (
    SELECT
       name,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) as number,
       AVG(mark) OVER (PARTITION BY name) AS nameavg
    FROM table
    ) foo
ORDER BY
   nameavg, number

However, PARTITION BY name ORDER BY name is meaningless. Each partition has random order because the sort isthe partition

但是，PARTITION BY name ORDER BY name 是没有意义的。每个分区都有随机顺序，因为排序是分区

I suspect you want something like this where ROW_NUMBER is based on AVG

我怀疑你想要这样的东西，其中 ROW_NUMBER 基于 AVG

SELECT
    name, number
FROM
    (
    SELECT
       name,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY nameavg) AS number
    FROM
        (
        SELECT
           name,
           AVG(mark) OVER (PARTITION BY name) AS nameavg
        FROM table
        ) foo
    ) bar
ORDER BY
    number

Or more traditionally (but name is collapsed for the average)

或更传统的（但名称已折叠为平均值）

SELECT
    name, number
FROM
    (
    SELECT
       name,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY nameavg) AS number
    FROM
        (
        SELECT
           name,
           AVG(mark) AS nameavg
        FROM
           table
        GROUP BY
           name
        ) foo
    ) bar
ORDER BY
    number

You can maybecollapse the derived foo and bar into one with

您可以或许崩溃派生foo和酒吧为一家拥有

ROW_NUMBER() OVER (PARTITION BY name ORDER BY AVG(mark))

But noneof this makes sense: I understand that your question is abstract about howit works bit it is unclear question. It would make more sense if you described what you want in plain English and with sample input and output

但这一切都没有意义：我知道您的问题是关于它如何工作的抽象问题，这是一个不清楚的问题。如果您用简单的英语和示例输入和输出描述您想要的内容会更有意义