Top 2 orders for each customer
Rank rows inside each group with ROW_NUMBER and keep the first few.
SELECT customer_id, order_id, amount, rn
FROM (
SELECT customer_id, order_id, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id) AS rn
FROM orders
) ranked
WHERE rn <= 2
ORDER BY customer_id, rn;How it works
PARTITION BY customer_id restarts the numbering for each customer, and ORDER BY amount DESC numbers the biggest order first. The outer query keeps rn 1 and 2.
The window function cannot be used directly in WHERE, which is why it sits in a subquery (a CTE works the same way).
Order by a second column (here order_id) so equal amounts always come out in the same order.
What your tables need
- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)
Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).
Small example
Input data
orders
order_id | customer_id | order_date | amount | status
1 | 1 | 2024-01-05 | 100 | paid
2 | 1 | 2024-02-10 | 150 | paid
3 | 2 | 2024-01-20 | 200 | paid
4 | 2 | 2024-03-02 | 50 | refunded
5 | 3 | 2024-02-14 | 300 | paid
6 | 1 | 2024-03-15 | 250 | paid
7 | 3 | 2024-03-20 | 120 | paid
8 | 2 | 2024-03-22 | 200 | paidExpected result
customer_id | order_id | amount | rn
1 | 6 | 250.0 | 1
1 | 2 | 150.0 | 2
2 | 3 | 200.0 | 1
2 | 8 | 200.0 | 2
3 | 5 | 300.0 | 1
3 | 7 | 120.0 | 2Common mistakes and limitations
- Using ROW_NUMBER when ties should both be kept. RANK gives tied rows the same number and may return more than N rows. - Forgetting PARTITION BY, which ranks across the whole table instead of inside each group. - Filtering on the window function in WHERE of the same query. It must be in an outer query.