PostgreSQL 18 reference · Window Functions
PostgreSQL ROW_NUMBER() Function
Number the rows of each window partition 1, 2, 3, … in ORDER BY order. Ties get different numbers.
PostgreSQL 18 Since 8.4
Syntax
row_number() OVER (PARTITION BY ... ORDER BY ...)
Return value
bigint.
Examples
SELECT row_number() OVER (ORDER BY price DESC) AS n, name, price
FROM products
LIMIT 3;
Output
n | name | price ---+---------------------+-------- 1 | 4K Monitor | 329.00 2 | Robot Kit | 120.00 3 | Mechanical Keyboard | 89.00 (3 rows)
Run this on your computer — see “Get Started” for setup.
SELECT department, name, salary
FROM (SELECT *, row_number() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) AS ranked
WHERE rn = 1
ORDER BY department;
Output
department | name | salary -------------+---------------+-------- Engineering | Omar Haddad | 210000 Management | Sofia Reyes | 250000 Sales | Hannah Okafor | 150000 Support | Ravi Kumar | 65000 (4 rows)
Run this on your computer — see “Get Started” for setup.
SELECT DISTINCT ON (department) … ORDER BY department, salary DESC is a PostgreSQL shortcut for the same
"first row per group" query.
Version history
| Version | Change |
|---|---|
| PostgreSQL 8.4 | Added |
In other languages
ROW_NUMBER()· SQLite
Related
RANK()· Rank rows by ORDER BY, giving ties the same rank and leaving gaps after them (1, 2, 2, 4).DENSE_RANK()· Rank rows by ORDER BY, giving ties the same rank with no gaps after them (1, 2, 2, 3).- SQL Window Functions · tutorial