SQL: Find the Top 2 Orders for Every Customer Using Window Functions
One SQL problem I often see people get wrong is:
How do you find the top 2 orders for every customer?
Finding the top 2 orders overall is easy.
The interesting part is “for every customer.”
Let’s take a simple example.
Sample Data
CID| OID| AMT
C001| 1001| 12000
C001| 1002| 7500
C001| 1003| 9000
C002| 1004| 15000
C002| 1005| 6000
C002| 1006| 11000
C003| 1007| 5000
C003| 1008| 18000
C003| 1009| 12000
We want the top 2 orders for each customer based on order amount.
The expected result is:
CID| OID| AMT
C001| 1001| 12000
C001| 1003| 9000
C002| 1004| 15000
C002| 1006| 11000
C003| 1008| 18000
C003| 1009| 12000
Why a Simple ORDER BY Is Not Enough
A common first attempt might be:
SELECT *
FROM orders
ORDER BY amt DESC
FETCH FIRST 2 ROWS ONLY;
This returns the top 2 orders from the entire table.
But that is not what we need.
We need the ranking to start again for each customer.
That is where SQL window functions become useful.
If you want to learn more about window functions and analytic functions, I’ve also covered the topic in more detail here:
SQL Window Functions / Analytic Functions
Using ROW_NUMBER()
Here is the solution:
SELECT cid,
oid,
amt
FROM (
SELECT cid,
oid,
amt,
ROW_NUMBER() OVER (
PARTITION BY cid
ORDER BY amt DESC
) AS rn
FROM orders
)
WHERE rn <= 2;
The key part is:
ROW_NUMBER() OVER (
PARTITION BY cid
ORDER BY amt DESC
)
What does it do?
PARTITION BY cid
Divides the data customer-wise.
ORDER BY amt DESC
Sorts each customer’s orders from highest amount to lowest.
ROW_NUMBER()
Assigns a sequence number to the orders within each customer.
The result before applying "WHERE rn <= 2" looks like this:
CID| OID| AMT| RN
C001| 1001| 12000| 1
C001| 1003| 9000| 2
C001| 1002| 7500| 3
C002| 1004| 15000| 1
C002| 1006| 11000| 2
C002| 1005| 6000| 3
C003| 1008| 18000| 1
C003| 1009| 12000| 2
C003| 1007| 5000| 3
Then:
WHERE rn <= 2
keeps only the first two rows for each customer.
What If Two Orders Have the Same Amount?
This is where "ROW_NUMBER()", "RANK()", and "DENSE_RANK()" behave differently.
ROW_NUMBER()
Every row gets a unique number.
For example:
OID| AMT| RN
1001| 12000| 1
1002| 12000| 2
If you need exactly 2 rows per customer, "ROW_NUMBER()" is usually the right choice.
RANK()
Rows with the same amount receive the same rank.
OID| AMT| RANK
1001| 12000| 1
1002| 12000| 1
1003| 9000| 3
Notice that rank 2 is skipped.
DENSE_RANK()
Rows with the same amount receive the same rank, but there are no gaps.
OID| AMT| RANK
1001| 12000| 1
1002| 12000| 1
1003| 9000| 2
So the function you choose depends on the requirement.
If you need:
«Exactly 2 orders for every customer»
use "ROW_NUMBER()".
If you need:
«All orders belonging to the top 2 amount levels»
"DENSE_RANK()" may be a better choice.
A Pattern Worth Remembering
Whenever you see a requirement like:
«Top N records for each customer, department, product, category, or region»
think about this pattern:
PARTITION BY
+
ORDER BY
+
ROW_NUMBER / RANK / DENSE_RANK
The same approach can be used for many practical SQL problems:
- Top 3 products for each category
- Highest-paid employees in each department
- Latest 2 transactions for each customer
- Top 5 sales for each region
- Most recent record for each employee
The table and business requirement may change, but the underlying SQL pattern is often the same.
Try This Yourself
Now change the requirement:
Find the latest 2 orders for every customer.
What would you change in the query?
The answer is in the "ORDER BY" inside the window function.
That small change is worth understanding because learning the pattern is much more useful than simply memorizing one SQL query.
Sankalan Data Tech
Practical SQL, Python and Data Engineering learning.
Top comments (0)