ORDER BY ROW_NUMBER

Question

UPD: thanks for everyone, the topic is closed, after sleep, I understand everything =)

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

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 - this is what ROW_NUMBER () does

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

will be displayed

name|number
Pete 1

And this time, I don’t understand how ORDER BY works with the ROW_NUMBER () function. Can someone explain this to me?

Answer 1

ROW_NUMBER, SELECT ORDER BY. . ( ).

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

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

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

, AVG , .

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

DESC, pete , .

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

SQL

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

Answer 2

ROW_NUMBER: , , . , CTEs

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

PARTITION BY AND ORDER BY . ,

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

( )

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

, foo bar

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

: , , , . , , ,