ROW_NUMBER()

Syntax

How it works

ROW_NUMBER() is a ranking window function. For every row it returns a whole number, starting at 1, that reflects the row position within its partition according to the ORDER BY inside the OVER() clause. Unlike an aggregate, it does not collapse rows: every input row keeps its own output row and simply gains a number.

The key property of ROW_NUMBER() is that it never ties. Even when two rows have identical ordering values, they still receive different numbers, and the tie is broken arbitrarily unless you add a further column to the ORDER BY. This is what separates it from RANK() and DENSE_RANK(), which deliberately give tied rows the same value.

Add PARTITION BY and the counter resets to 1 at the start of each group, which is what makes ROW_NUMBER() the standard tool for top-N-per-group queries, deduplication and stable pagination. It is available in MySQL 8.0 and later, PostgreSQL, SQL Server and SQLite 3.25 and later. Older MySQL 5.x and pre-3.25 SQLite do not support window functions at all.

Examples

Number every row in a result set

SELECT ROW_NUMBER() OVER (ORDER BY created_at) AS rn,
       id, created_at
FROM orders
ORDER BY created_at;

rn | id | created_at
---+----+-----------
 1 |  7 | 2026-01-02
 2 |  3 | 2026-01-02
 3 |  9 | 2026-01-05

Number rows within each group (PARTITION BY)

-- Restart the counter for each customer
SELECT customer_id, id, amount,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY amount DESC) AS rn
FROM orders
ORDER BY customer_id, rn;

customer_id | id | amount | rn
------------+----+--------+---
        101 | 12 |    900 |  1
        101 |  4 |    350 |  2
        102 | 18 |    500 |  1

Top-N per group (keep the highest order per customer)

-- ROW_NUMBER() cannot go in WHERE, so wrap it in a CTE
WITH ranked AS (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer_id, id, amount
FROM ranked
WHERE rn = 1;

customer_id | id | amount
------------+----+-------
        101 | 12 |    900
        102 | 18 |    500
        103 | 22 |    740

Deduplicate: keep the first row of each group

-- Keep one row per email, the most recently updated one
WITH dedup AS (
  SELECT id, email, updated_at,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY updated_at DESC) AS rn
  FROM customers
)
SELECT id, email, updated_at
FROM dedup
WHERE rn = 1;

id | email           | updated_at
---+-----------------+-----------
 5 | ali@example.com | 2026-06-30
 8 | sara@shop.com   | 2026-07-01

Keyset-friendly pagination

-- Number rows in a stable order, then slice a page
WITH numbered AS (
  SELECT id, title,
         ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
  FROM articles
)
SELECT id, title
FROM numbered
WHERE rn BETWEEN 21 AND 30
ORDER BY rn;

id  | title
----+------------------
 41 | Indexing basics
 42 | Joins explained
 43 | Window functions

Common mistakes

-- No ORDER BY: the numbering is meaningless
-- and can differ on every run
SELECT id,
       ROW_NUMBER() OVER () AS rn
FROM orders;

-- Always give OVER() an ORDER BY so the
-- sequence is defined and repeatable
SELECT id,
       ROW_NUMBER() OVER (ORDER BY created_at) AS rn
FROM orders;

-- Window functions are not allowed in WHERE
SELECT customer_id, id, amount
FROM orders
WHERE ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC) = 1;

-- Compute it in a subquery or CTE, then filter
WITH ranked AS (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer_id, id, amount
FROM ranked
WHERE rn = 1;

-- ORDER BY has ties, so rows with the same amount
-- get numbered in an unpredictable order
SELECT id, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM orders;

-- Add a unique tiebreaker for a stable result
SELECT id, amount,
       ROW_NUMBER() OVER (
         ORDER BY amount DESC, id) AS rn
FROM orders;

Performance

ROW_NUMBER() requires the engine to sort each partition according to the ORDER BY inside OVER(). On a large table with no supporting index this sort dominates the cost, exactly like an ORDER BY on the whole result set.

An index whose columns match the PARTITION BY keys followed by the ORDER BY keys lets the optimiser read rows already in order and skip the sort entirely. For example, a partition on customer_id ordered by amount DESC is served well by an index on (customer_id, amount).

For top-N-per-group queries the CTE plus WHERE rn = 1 pattern still numbers every row before filtering. If N is tiny and the group count is huge, a correlated subquery or a lateral join can sometimes be cheaper, but ROW_NUMBER() is usually the clearest and fast enough choice. See the window functions guide for more patterns.

Interview questions

WITH ranked AS (
  SELECT department_id, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department_id, name, salary
FROM ranked
WHERE rn = 1;

WITH dedup AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY updated_at DESC) AS rn
  FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM dedup WHERE rn > 1);