[ACCEPTED]-Row numbering in PostgreSQL-row-number

Accepted answer

Score: 37

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

Also, your statement 8 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 7 are not unique then there is no guarantee 6 that they will even produce the same order. Perhaps 5 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 4 if you are running this query several times 3 with different offsets, you need to:

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

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.