SQL ROW_NUMBER(): Number Rows, Top-N Per Group and Remove Duplicates

ROW_NUMBER() gives each row a sequential number (1, 2, 3, …) in the order you choose. It sounds simple, but combined with PARTITION BY it solves some of the most common real-world problems: the top item per group, the latest record per customer, and removing duplicates.

Syntax#

ROW_NUMBER() OVER (
  [PARTITION BY column]
  ORDER BY column
)

ORDER BY decides the numbering order. PARTITION BY (optional) restarts the numbering for each group.

Number all rows#

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

name  | salary | rn
------+--------+---
Meera | 81000  | 1
Ravi  | 78000  | 2
Priya | 61000  | 3
Asha  | 52000  | 4
John  | 46000  | 5

Restart numbering per group#

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

name  | department  | salary | rn
------+-------------+--------+---
Meera | Engineering | 81000  | 1
Ravi  | Engineering | 78000  | 2
John  | Marketing   | 46000  | 1
Priya | Sales       | 61000  | 1
Asha  | Sales       | 52000  | 2

Row 1 in each department is its highest earner.

Top row per group#

Window functions cannot be used in WHERE, so compute the number in a subquery (or CTE) and filter outside it. The highest-paid employee in each department:

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

name  | department  | salary
------+-------------+-------
Meera | Engineering | 81000
John  | Marketing   | 46000
Priya | Sales       | 61000

Change rn = 1 to rn <= 3 for the top three per group. This "top-N per group" pattern is one of the most frequently asked SQL interview questions.

Latest record per group#

Each customer's most recent order:

SELECT order_id, customer_id, order_date, amount
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders o
) t
WHERE rn = 1
ORDER BY customer_id;

order_id | customer_id | order_date | amount
---------+-------------+------------+-------
102      | 1           | 2026-02-11 | 120
103      | 2           | 2026-02-20 | 560
104      | 5           | 2026-03-02 | 90

Removing duplicates#

Imagine a signups table where some people signed up twice:

SELECT email, signed_up,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY signed_up) AS rn
FROM signups
ORDER BY email, rn;

email   | signed_up  | rn
--------+------------+---
a@x.com | 2026-01-01 | 1
a@x.com | 2026-01-05 | 2
b@x.com | 2026-01-02 | 1
b@x.com | 2026-01-09 | 2
c@x.com | 2026-01-03 | 1

Every row with rn > 1 is a duplicate of an earlier signup. Keep rn = 1 to see each person once, or delete the others. (The exact DELETE syntax for this varies; in PostgreSQL you can delete by ctid, and in SQL Server you can delete directly from a CTE.)

ROW_NUMBER and ties#

ROW_NUMBER always gives different numbers, even when values are tied. Which tied row gets 1 and which gets 2 is arbitrary unless you add a tie-breaker:

ROW_NUMBER() OVER (ORDER BY score DESC, student)

If tied rows should share a position, use RANK or DENSE_RANK.

Common mistakes#

Practice#

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 2;

name | department  | salary
-----+-------------+-------
Ravi | Engineering | 78000
Asha | Sales       | 52000

Key takeaway#