Retrieving the Top 3 Orders per Customer in SQL Server

When working with SQL Server, you often need to retrieve a specific number of records per group. For instance, you might want to fetch top 3 most recent orders for each customer in your database. There are multiple ways to achieve this, but in this post we will explore only three methods: using CROSS APPLY, Common Table Expression (CTE) and a correlated subquery. Demo queries were written against Northwind database. Additionally, I’ve enabled IO and CPU statistics to get a general overview of performance, but I don’t consider this post as a deep performance analysis. It’s more about how we can quickly measure logical reads and CPU time.

 As I previously mentioned, we’re going to gather some stats about executed queries with:

SET STATISTICS IO, TIME ON;

Method 1: using CROSS APPLY

The CROSS APPLY operator is particularly useful for this kind of query because it allows you to apply a table-valued function to each row of an outer query. Here is how you can use it to get the top 3 orders for each customer.

SELECT c.CustomerID, A.OrderID, A.OrderDate
FROM dbo.Customers AS c
CROSS APPLY (
	SELECT TOP 3 orderid, OrderDate
	FROM dbo.Orders AS o
	WHERE o.CustomerID = c.CustomerID
	ORDER BY OrderDate, OrderID DESC) AS A;
GO

Method 2: Using Common Table Expressions (CTEs)

CTEs are useful for breaking down complex queries and can be combined with window functions to rank orders. This CTE ranks orders for each customer and then filters to include only the top 3 orders.

WITH RankedOrders AS (
    SELECT 
        o.CustomerID, 
        o.OrderID, 
        o.OrderDate,
        ROW_NUMBER() OVER (PARTITION BY o.CustomerID ORDER BY o.OrderDate DESC, o.OrderID DESC) AS rn
    FROM dbo.Orders AS o
)
SELECT 
    ro.CustomerID, 
    ro.OrderID, 
    ro.OrderDate
FROM 
    RankedOrders AS ro
WHERE 
    ro.rn <= 3
ORDER BY 
    ro.CustomerID, 
    ro.OrderDate DESC, 
    ro.OrderID DESC;
GO

Method 3: Using a Correlated Subquery

A correlated subquery can also be used to retrieve top 3 orders for each customer. This method involves filtering orders based on the results of a subquery. In this query, the subquery fetches the top 3 OrderID’s for each customer, and the outer query filters the orders based on these OrderID’s

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
JOIN dbo.Orders AS o 
	ON o.CustomerID = c.CustomerID
WHERE o.OrderID IN (
    SELECT TOP 3 o2.OrderID
    FROM dbo.Orders AS o2
    WHERE o2.CustomerID = c.CustomerID
    ORDER BY o2.OrderDate DESC, o2.OrderID DESC
)
ORDER BY c.CustomerID, o.OrderDate DESC, o.OrderID DESC;
GO

Conclusion

Based on the performance results of the three methods for retrieving the top 3 orders per customer in SQL Server, the most efficient was the CTE (method 1), followed by CROSS APPLY (method 2), and lastly, the Correlated Subquery (method 3).

Depending on specific requirements and the characteristics of your data, you can choose the method that best suits your needs. I encourage you to experiment with these techniques to find the most efficient and readable solution for your scenarios. Happy querying!