Numbering in sorted sql query?

Question

PostgreSQL

Hey! You need to number the top list of users!
That is, the highest rated user has number 1 and so on down

Sorting goes DESC from the user's rating

SELECT u.id, u.avatar_path, u.rating, ROW_NUMBER() over() AS num
FROM users AS u
ORDER BY u.rating DESC;

but my numbering starts not from 1, but from 12. Help me how to make the numbering independent of sorting.

Answer 1

You need to specify sorting in the window function. And then the ORDER BY query can be done by the num field.

SELECT u.id, u.avatar_path, u.rating, ROW_NUMBER() OVER (ORDER BY u.rating DESC) AS num
FROM users AS u
ORDER BY num ASC;