SQL Reference ยท Window Functions
SQL ROW_NUMBER() function
Reference for SQL ROW_NUMBER(): number rows 1, 2, 3 within a window, top-N per group, removing duplicates, pagination and why a tie-breaker matters.
ROW_NUMBER() is a window function that gives each row a unique sequential number, starting at 1, in the order defined by the ORDER BY inside its OVER() clause. With PARTITION BY the numbering restarts for every partition.
Unlike RANK(), it never produces ties: rows with equal sort values still get different numbers, in an order the database chooses unless you add more columns to the ORDER BY. That makes it the standard tool for "top N per group", for picking one row per key and for removing duplicates.
SELECT first_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS position FROM employees;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
ROW_NUMBER() OVER ( [ PARTITION BY expression [, ...] ] ORDER BY expression [ ASC | DESC ] [, ...] )
| Part | Description |
|---|---|
| (no arguments) | The parentheses after ROW_NUMBER are always empty. |
PARTITION BY |
Optional. Numbering restarts at 1 in each partition. |
ORDER BY |
Defines the numbering order. Technically optional in most databases, but then the order is arbitrary; SQL Server and Oracle require it. |
Returns: a positive integer (bigint in several databases). It is never NULL.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
ROW_NUMBER() |
3.25+ | 8.4+ | 8.0+ | 2005+ | Yes |
OVER without ORDER BY |
Allowed | Allowed | Allowed | Error | Error |
| Pseudo-column alternative | rowid (not the same thing) |
None | None | None | ROWNUM (assigned before sorting) |
-- SQL Server: a constant ORDER BY when you do not care about the order SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n, name FROM products; -- Oracle: ROWNUM is numbered before ORDER BY, so this does NOT give the top 3 SELECT * FROM products WHERE ROWNUM <= 3 ORDER BY price DESC;
Usage notes
- Add a tie-breaker. If two rows have the same
ORDER BYvalue, which one gets the lower number is unspecified and may change between runs. End theORDER BYwith a unique column such asid. - You cannot filter on it directly. A window function is not allowed in
WHERE. Put the query in a subquery or CTE and filter on the alias outside, as in the examples above. (Some databases offerQUALIFYfor this; SQLite, PostgreSQL, MySQL, SQL Server and Oracle do not.) - Top N per group:
rn <= Nafter partitioning. If ties at the boundary should all be kept, useRANK()instead. - Deduplicating: number the rows within each duplicate key, then keep
rn = 1, or delete the rows withrn > 1. - Pagination:
LIMIT ... OFFSETis simpler for plain paging. Row numbers are useful when the page is cut from an already-numbered result. - Not related to
rowid.rowidis a stored identity of a row in SQLite;ROW_NUMBER()is computed per query. - Cost: the database sorts rows by the partition and order columns. An index in that order can avoid the sort.