ROW_NUMBER()
SQL · Window Functions
Theory
ROW_NUMBER() gives each row a unique sequence number, 1, 2, 3 and so on, in the order set by the window ORDER BY. With PARTITION BY, numbering restarts at 1 in every partition. Ties do not matter: two employees with the same salary still get different numbers, and which of them gets the smaller number is not guaranteed unless you add a tie-breaker column.
This makes ROW_NUMBER the standard tool for 'top N per group' and for removing duplicates. For the highest-paid person in each department, number rows with PARTITION BY dept ORDER BY salary DESC and keep rn = 1. For deduplication, partition by the columns that define a duplicate (such as email), order by the latest timestamp, and keep rn = 1. Because window functions cannot appear in WHERE, you filter on rn in an outer query or CTE (Snowflake and some others also offer QUALIFY for this).
Why we use it
You often need exactly one row per group: the latest order per customer, the top earner per department, one copy of each duplicate record. ROW_NUMBER picks exactly one row (or exactly N) even when values tie, which RANK cannot promise.
When to use it
Use ROW_NUMBER when you need exactly N rows per group or a strict de-duplication. If tied rows should share a position (leaderboards), use RANK or DENSE_RANK instead.
How it works
- Rows split by PARTITION BY (optional)
- Sort each partition by the window ORDER BY
- Give the first row 1, then add 1 for each next row
- New partition starts? Yes: Reset the counter to 1. No: Keep counting, ties still get new numbers.
- Outer query filters, e.g. WHERE rn = 1
- One (or N) row per group
Syntax
ROW_NUMBER() OVER (PARTITION BY col ORDER BY col)Example
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;Key points
- Never produces ties: always strictly 1, 2, 3...
- Common for picking the top N per group (filter rn <= N)
- Standard way to de-duplicate: keep rn = 1 per duplicate key
- Add a tie-breaker in ORDER BY or the order of ties is random
- Filter rn in a subquery/CTE; it cannot be used in WHERE directly