ROW_NUMBERFunctions
Buy Me A Coffee

Assign a sequential integer to each row within a result set or partition, starting at 1. Rows with equal values receive different numbers (unlike RANK).

Assigns a unique sequential integer to each row within a partition, starting at 1. Useful for deduplication (taking the first row per group) and pagination. Combine with DISTINCT ON in PostgreSQL for efficient top-N per group.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 ROW_NUMBER() fully supported since MySQL 8.0. Not available in MySQL 5.x — use user-defined variables as a workaround.
PostgreSQL ✓ Supported 8.4 ROW_NUMBER() fully supported. Commonly used with a CTE to filter to a specific rank (top-N per group pattern).
SQL Server ✓ Supported 2005 ROW_NUMBER() fully supported since SQL Server 2005. The ORDER BY inside OVER() is required for ROW_NUMBER (unlike some other window functions).
Oracle ✓ Supported 8i ROW_NUMBER() fully supported. Oracle also has ROWNUM (a pseudocolumn, not a window function) which assigns numbers before ORDER BY is applied — they behave differently.
SQLite ✓ Supported 3.25.0 ROW_NUMBER() fully supported since SQLite 3.25.0 (September 2018).

Details

The top-N per group pattern (get the highest-paid employee per department) is one of the most common uses. Oracle's ROWNUM pseudocolumn is a frequent source of confusion — it's not equivalent to ROW_NUMBER() OVER () because it assigns numbers before sorting. MySQL 5.x had no ROW_NUMBER(), which caused years of painful workarounds with session variables.

Standard Syntax

SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2005 Oracle: Since 8i SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

ROW_NUMBER() fully supported since MySQL 8.0. Not available in MySQL 5.x — use user-defined variables as a workaround.

SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees; -- Pre-8.0 workaround: SELECT name, @rn := @rn + 1 AS rn FROM employees, (SELECT @rn := 0) r;

PostgreSQL

ROW_NUMBER() fully supported. Commonly used with a CTE to filter to a specific rank (top-N per group pattern).

SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees; -- Top-1 per group: WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees ) SELECT * FROM ranked WHERE rn = 1;

SQL Server

ROW_NUMBER() fully supported since SQL Server 2005. The ORDER BY inside OVER() is required for ROW_NUMBER (unlike some other window functions).

SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees;

Oracle

ROW_NUMBER() fully supported. Oracle also has ROWNUM (a pseudocolumn, not a window function) which assigns numbers before ORDER BY is applied — they behave differently.

SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees; -- ROWNUM is NOT the same: SELECT ROWNUM, name FROM employees; -- assigned before sorting

SQLite

ROW_NUMBER() fully supported since SQLite 3.25.0 (September 2018).

SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees;