🚀 Introduction
One of the most common problems in SQL development is retrieving the top N rows per group. For example, if you’re working with sales data, you might want to fetch the top 3 sales per customer or the highest salary per department. This query pattern is widely used in reporting, analytics, and data visualization.
In this guide, we’ll explain step by step how to write SQL queries to get top N records per group using simple and advanced techniques. We’ll cover approaches for popular databases like SQL Server, MySQL, and PostgreSQL.
🎯 Why Do We Need Top N Records Per Group?
Imagine you have an Orders table, and you want to get the top 2 recent orders for each customer. Without grouping, you’d only get the overall top records. With grouping, you can analyze data per category, user, or department.
Real-world use cases include:
Getting the top 3 highest-paid employees per department
Fetching the latest 5 transactions per user
Retrieving the top 2 selling products in each region
🛠️ Method 1. Using ROW_NUMBER() in SQL Server
The most common solution uses the ROW_NUMBER() window function.
Example: Top 2 orders per customer
SELECT *
FROM (
SELECT
CustomerID,
OrderID,
OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowNum
FROM Orders
) AS Ranked
WHERE RowNum <= 2;
Explanation
ROW_NUMBER() assigns a unique number to each row within a group.
PARTITION BY CustomerID ensures numbering starts fresh for each customer.
ORDER BY OrderDate DESC sorts records so the most recent orders are ranked first.
Finally, WHERE RowNum <= 2 filters top 2 per group.
🛠️ Method 2. Using RANK() or DENSE_RANK()
Sometimes you want ties to be included. Instead of ROW_NUMBER(), you can use RANK() or DENSE_RANK().
SELECT *
FROM (
SELECT
Department,
EmployeeName,
Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employees
) AS Ranked
WHERE Rank <= 3;
Difference
🛠️ Method 3. Using CROSS APPLY (SQL Server)
Another efficient way in SQL Server is with CROSS APPLY.
SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM Customers c
CROSS APPLY (
SELECT TOP 2 *
FROM Orders o
WHERE o.CustomerID = c.CustomerID
ORDER BY o.OrderDate DESC
) o;
Why it works: For each customer, SQL applies a subquery to fetch top 2 rows.
🛠️ Method 4. MySQL and PostgreSQL (LIMIT + Subquery)
In MySQL and PostgreSQL (before window functions were supported), developers used correlated subqueries.
SELECT o1.*
FROM Orders o1
WHERE (
SELECT COUNT(*)
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID
AND o2.OrderDate > o1.OrderDate
) < 2;
Here, each row checks how many newer rows exist. If fewer than 2, then it’s in the top 2.
⚠️ Common Mistakes
Forgetting PARTITION BY, which groups the data.
Using TOP without applying grouping logic.
Not ordering correctly, which gives wrong results.
📝 Best Practices
Always use ROW_NUMBER() when you need unique ordering.
Use RANK() or DENSE_RANK() if ties matter.
Make sure you filter correctly (WHERE RowNum <= N).
For very large datasets, ensure indexes exist on partition and order by columns.
🎯 SQL Server Skill Challenge — Test What You Learned!
Now that you’ve learned how to fetch top N records per group in SQL, it’s time to put your skills to the test! 🎉
👉 Take the SQL Server Skill Challenge and apply what you’ve learned. You’ll earn Sharp Tokens 🏆 as a reward for your knowledge!
👉 Don’t just read—apply your skills and get rewarded today!.
🎯 Conclusion
Getting the top N records per group in SQL is a common but tricky problem. With the right use of window functions (ROW_NUMBER, RANK, DENSE_RANK) or techniques like CROSS APPLY and LIMIT, you can write efficient queries for SQL Server, MySQL, and PostgreSQL.