SQL practice › SQL reference › Top N per group

SQL top N per group: the top rows of each group, with queries you can run

To get the top N rows of each group, number the rows inside each group with a window function, then keep the numbers up to N in an outer query. ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) numbers each department's employees from the best paid down; wrap it in a CTE and filter with WHERE n <= 2.

ORDER BY ... LIMIT can't do this: LIMIT cuts the whole result once, not once per group. And before you pick the function, decide what a tie should do. ROW_NUMBER returns exactly N rows per group, while RANK and DENSE_RANK keep the ties.

Skip to the 4 exercises ↓

Syntax

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS n
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE n <= 2
PARTITION BY department
The group. The numbering starts again at 1 in each department.
ORDER BY salary DESC
What "top" means: the highest salary gets number 1. For the latest row of each group it would be a date, descending.
ROW_NUMBER()
Exactly N rows per group, ties cut. RANK() or DENSE_RANK() in its place keep tied rows.
WITH ranked AS (...)
The number has to exist before it can be filtered, and a window function can't be used in WHERE. So it is computed in a CTE (or a subquery in FROM).
WHERE n <= 2
N. Write n = 1 for the single top row of each group.

The data

Nine employees in three departments. Engineering and Sales each have two people on the same salary (Zoe and Kim on 88000, Omar and Lena on 58000), which is what makes "top 2" interesting.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22

Why ORDER BY and LIMIT aren't enough

Sorting by salary and cutting with LIMIT gives the top rows of the whole table. There is no way to tell LIMIT to start counting again in each department.

Live queryORDER BY and LIMIT: the top 3 of the company, not of each department
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentnamesalary
EngineeringRaj95000
EngineeringZoe88000
EngineeringKim88000

The highest paid employee in each department

Number the rows inside each department, best paid first, and keep number 1.

Live queryROW_NUMBER in a CTE, n = 1 outside
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentnamesalary
EngineeringRaj95000
SalesMaya72000
SupportNora46000

The top 2 per department, and what ties do

For the top 2, the filter becomes n <= 2. Now the ties matter, because in two departments second place is shared.

Live queryTop 2 with ROW_NUMBER: exactly two rows each, ties cut
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 6 rows
departmentnamesalary
EngineeringRaj95000
EngineeringZoe88000
SalesMaya72000
SalesOmar58000
SupportNora46000
SupportTheo43000
FunctionWhat n <= N keepsRows here for N = 2, N = 3
ROW_NUMBER()Exactly N rows per group (fewer if the group is smaller). A tie at the edge is cut, so add a tie-breaker to the ORDER BY to choose who stays.6, 8
RANK()The top N places, with everyone tied for a place. Can return more than N rows.8, 8
DENSE_RANK()Everyone who has one of the N highest values.8, 9

For N = 3, RANK leaves Ivan out of Engineering (three people earn more than him) and DENSE_RANK keeps him (his 81000 is the department's third highest salary). The three functions are compared row by row in RANK vs DENSE_RANK vs ROW_NUMBER.

Each customer's latest order

The same pattern answers "the most recent row of each group": partition by the customer and order by the date, newest first. These are a shop's customers and orders. Eve has no orders.

customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Live queryThe most recent order of each customer
Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 4 rows
nameproductorder_date
AvaLamp2026-03-03
BenDesk2026-02-11
ChloeChair2026-03-14
DanLamp2026-02-20

The older ways: a join on MAX, or a correlated subquery

Before window functions, the top row of each group was found in two steps: get each group's maximum with GROUP BY, then join back to the table to find the rows that have it.

Live queryJoin each department's MAX back to the table
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentnamesalary
SalesMaya72000
EngineeringRaj95000
SupportNora46000

For the top N, the old way is a correlated subquery that counts, for each row, how many rows of its group are ahead of it.

Live queryTop 2 per department by counting who earns more
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 8 rows
departmentnamesalary
SalesMaya72000
SalesOmar58000
SalesLena58000
EngineeringRaj95000
EngineeringZoe88000
SupportNora46000
SupportTheo43000
EngineeringKim88000

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on these tables and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.

Exercise 1The newest hire of each department

For each department return the department, and the name and hire_date of the employee hired most recently. No two employees share a hire date.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
WITH ranked AS (
  SELECT department, name, hire_date,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date DESC) AS n
  FROM employees
)
SELECT department, name, hire_date
FROM ranked
WHERE n = 1
Result · 3 rows
departmentnamehire_date
EngineeringKim2022-08-22
SalesLena2022-02-14
SupportTheo2023-04-03

PARTITION BY department numbers each department separately and ORDER BY hire_date DESC gives number 1 to its newest hire: Kim, Lena and Theo. The hire dates are all different, so RANK would do the same here.

Exercise 2The two highest salaries of each department, ties included

Return the department, name and salary of every employee whose salary is one of the two highest salaries in their department. Two people on the same salary both count, and a shared salary counts as one salary.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
WITH ranked AS (
  SELECT department, name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS n
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE n <= 2
Result · 8 rows
departmentnamesalary
EngineeringRaj95000
EngineeringZoe88000
EngineeringKim88000
SalesMaya72000
SalesOmar58000
SalesLena58000
SupportNora46000
SupportTheo43000

DENSE_RANK numbers the different salaries of each department 1, 2, 3, so n <= 2 keeps everyone on the two highest ones: eight people, with only Ivan left out. RANK returns the same rows on this data, but not when the top salary is shared: after two people at 1 it jumps to 3.

Exercise 3Each customer's latest order

For each customer who has ordered, return the customer's name, and the product and order_date of their most recent order. No customer has two orders on the same date.

Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
WITH ranked AS (
  SELECT customer_id, product, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS n
  FROM orders
)
SELECT c.name, r.product, r.order_date
FROM ranked r
JOIN customers c ON c.id = r.customer_id
WHERE r.n = 1
Result · 4 rows
nameproductorder_date
AvaLamp2026-03-03
BenDesk2026-02-11
ChloeChair2026-03-14
DanLamp2026-02-20

The CTE numbers each customer's orders from the newest to the oldest, and the outer query keeps number 1 and joins customers for the name. Eve has no orders, so she isn't in the result. A join on each customer's MAX(order_date) returns the same four rows.

Exercise 4Each customer's largest order

For each customer return their customer_id, and the id and amount of their largest order: exactly one row per customer. If a customer has two orders of the same largest amount, take the more recent one.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
WITH ranked AS (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_date DESC) AS n
  FROM orders
)
SELECT customer_id, id, amount
FROM ranked
WHERE n = 1
Result · 4 rows
customer_ididamount
1101240
2105240
3104180
410635

ROW_NUMBER returns exactly one row per customer. The second sort key, order_date DESC, decides which order is number 1 when two have the same amount. Without it the database would choose, and with RANK both would come back.

Questions people ask

How do I select the top N rows from each group in SQL?

Number the rows inside each group with ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) in a CTE or a subquery, then filter in the outer query with WHERE n <= N. This problem is also searched for as "greatest N per group".

Why can't I write WHERE ROW_NUMBER() OVER (...) <= 2 directly?

Because WHERE chooses the rows before window functions are computed, so the number doesn't exist yet. The window function goes in the SELECT list of an inner query, and the outer query filters on its column.

Can't I just use GROUP BY with MAX?

MAX(salary) with GROUP BY department gives each department's top salary, but not the name that goes with it. SELECT department, name, MAX(salary) ... GROUP BY department is not valid in PostgreSQL, because name is neither in the GROUP BY nor inside an aggregate. SQLite accepts it and takes name from the row with the maximum, picking one arbitrarily when several rows share it; its documentation notes that most other databases reject such columns. Join the maximum back to the table, or use ROW_NUMBER.

ROW_NUMBER, RANK or DENSE_RANK for top N per group?

ROW_NUMBER when you need exactly N rows per group (add a tie-breaker to the ORDER BY). RANK when everyone tied for one of the top N places should be kept. DENSE_RANK when you mean the N highest values and everyone who has them.

Is there a shorter way in PostgreSQL?

For the single top row of each group, PostgreSQL has SELECT DISTINCT ON (department) ... ORDER BY department, salary DESC, which keeps the first row of each department in that order. It is a PostgreSQL extension to standard SQL, and it only does N = 1. SQLite doesn't have it, so it won't run in the boxes on this page.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

RANK vs DENSE_RANK vs ROW_NUMBER, side by sideSQL window functions: OVER and PARTITION BY, live queriesSQL second highest salary, with tiesSQL subquery vs CTE: the same question three waysSQL GROUP BY, with live queriesSQL interview questions, with answersSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: Window Functions (tutorial), SQLite documentation: Window Functions, PostgreSQL documentation: SELECT, SQLite documentation: SELECT (bare columns in an aggregate query), MySQL 8.4 Reference Manual: Window Function Descriptions. Last checked November 2026.