Row numbering in PostgreSQL

Question

How to get row number in PostgreSQL when the results are ordered by some column?

e.g.

SELECT 30+row_number() AS position, * 
FROM users 
ORDER BY salary DESC 
LIMIT 30 
OFFSET 30

I supposed that the query would return list like this:

position | name | salary
31       | Joy  | 4500
32       | Katie| 4000
33       | Frank| 3500

Actually i have to duplicate the ORDER clause into the query to make it functional:

SELECT 30+row_number(ORDER BY salary DESC) AS position, * 
FROM users 
ORDER BY salary DESC 
LIMIT 30 
OFFSET 30

Is there any other way how to return ordered and numbered results without necessity of duplicating the code?

I know this can be solved by incrementing some variable in the app itself, but i wanna do this at the database layer and return to the app already numbered results...

Answer 1

no - the order by in the windowing function and the order by clause of the select statement are functionally two different things.

Also, your statement produces: ERROR: window function call requires an OVER clause, so:

SELECT 30+row_number(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

should be:

SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

Note that if salaries are not unique then there is no guarantee that they will even produce the same order. Perhaps it would be better to do:

SELECT * 
FROM ( SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * 
       FROM users )
ORDER BY position LIMIT 30 OFFSET 30

Also note that if you are running this query several times with different offsets, you need to:

1. set your isolation level to serializable
2. make sure that whatever you are ordering by is unique

or you may get duplicates and missing rows. See the comments on this answer for why.