A
A
Alex Fedorov2022-02-16 11:01:11
PostgreSQL
Alex Fedorov, 2022-02-16 11:01:11

Numbering in sorted sql query?

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 the question

In order to leave comments, you need to log in

1 answer(s)
M
Michael, 2022-02-16
@Akela_wolf

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;

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question