ROW_NUMBER

ROW_NUMBER() is a window function that assigns a unique, sequential integer to each row within a result set, starting at 1. Unlike an aggregate function, it does not collapse rows — every input row keeps its place, but now carries a number that reflects its position according to the ordering you specify.

The basic syntax pairs ROW_NUMBER() with an OVER clause that defines two things: how the rows are grouped into independent partitions (PARTITION BY), and how rows are ordered within each partition before numbering (ORDER BY).

SQL

SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

PARTITION BY resets the numbering for each department, so every department starts its own count at 1. If PARTITION BY is omitted, ROW_NUMBER() treats the entire result set as a single partition and numbers every row from 1 to N.

Sample data and output

Given this small employees table:

SQL

employee_id | department | salary
------------+------------+-------
1           | Sales      | 50000
2           | Sales      | 65000
3           | Sales      | 65000
4           | IT         | 72000
5           | IT         | 60000

Running the query above produces:

employee_id | department | salary | rank_in_dept
------------+------------+--------+-------------
2           | Sales      | 65000  | 1
3           | Sales      | 65000  | 2
1           | Sales      | 50000  | 3
4           | IT         | 72000  | 1
5           | IT         | 60000  | 2

Notice that employees 2 and 3 have the same salary, yet ROW_NUMBER() still assigns them different numbers (1 and 2). This is expected behavior, not a bug.

Warning

When the ORDER BY expression does not produce a strict, unique ordering (as with tied salaries above), the database is free to break the tie in any order it likes. Two runs of the same query, or the same query run on a different day after a data update, can assign row numbers to tied rows differently. If deterministic, repeatable results matter, add enough columns to ORDER BY to make the ordering fully unique — for example, break salary ties with employee_id: ORDER BY salary DESC, employee_id ASC.

Practical use case: deduplication

One of the most common real-world uses of ROW_NUMBER() is removing duplicate rows while keeping exactly one "best" row per group — for example, keeping only the most recent order for each customer, or the latest snapshot of a record that was updated multiple times.

Suppose a table of customer orders has accidental duplicates:

SQL

order_id | customer_id | order_date  | amount
---------+-------------+-------------+-------
101      | 1           | 2024-01-05  | 250.00
102      | 1           | 2024-03-12  | 180.00
103      | 2           | 2024-02-20  | 400.00
104      | 2           | 2024-02-20  | 400.00

Orders 103 and 104 look like duplicate entries for the same order. To keep only one row per (customer_id, order_date, amount) combination, number the rows within each duplicate group and filter to the first one:

SQL

WITH numbered AS (
  SELECT
    order_id,
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id, order_date, amount
      ORDER BY order_id
    ) AS rn
  FROM orders
)
SELECT order_id, customer_id, order_date, amount
FROM numbered
WHERE rn = 1;

order_id | customer_id | order_date  | amount
---------+-------------+-------------+-------
101      | 1           | 2024-01-05  | 250.00
102      | 1           | 2024-03-12  | 180.00
103      | 2           | 2024-02-20  | 400.00

Row 104 was dropped because it was the second occurrence (rn = 2) of an identical combination. This pattern — number, then filter to rn = 1 — is the standard way to deduplicate rows in SQL without deleting anything until you are sure which rows to keep.

Tip

The same numbering trick also works for the opposite problem — keeping only the newest row per group. Just order by a timestamp DESC instead of an id ascending, and filter to rn = 1 to keep the latest record and discard older, stale versions.

ROW_NUMBER vs filtering with LIMIT

A plain LIMIT clause only lets you cap the total number of rows returned by a query. It cannot express "give me the top 2 rows per department" because LIMIT applies globally, not per group. ROW_NUMBER() combined with PARTITION BY and a WHERE filter (via a CTE or subquery, since window functions cannot appear directly in a WHERE clause) is the standard way to get top-N-per-group results.

SQL

-- Top 2 highest-paid employees in each department
WITH ranked AS (
  SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT employee_id, department, salary
FROM ranked
WHERE rn <= 2;

Note

ROW_NUMBER() cannot be used directly inside a WHERE clause because window functions are evaluated after WHERE and GROUP BY, but before ORDER BY and LIMIT, in the logical query processing order. Wrap the ROW_NUMBER() query in a CTE or subquery, then filter on the alias in the outer query, as shown above.