Find the Most Recent Order for Each Customer Using MS SQL Server Query?

To find the most recent order for each customer, you can use the ROW_NUMBER() window function to rank the orders for each customer based on the order date and then select the most recent one.

Assume you have a table named Orders with the following columns:

• OrderID
• CustomerID
• OrderDate

Here’s how you can write a query to get the most recent order for each customer:

SQL Query

WITH RankedOrders AS (

    SELECT

        OrderID,

        CustomerID,

        OrderDate,

        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn

    FROM Orders

)

SELECT

    OrderID,

    CustomerID,

    OrderDate

FROM RankedOrders

WHERE rn = 1;

Explanation:

1. Common Table Expression (CTE) RankedOrders:

   • ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn: This assigns a unique sequential integer to each order within the partition of each customer, ordered by OrderDate in descending order. The most recent order for each customer gets rn = 1.

2. Main Query:

   • WHERE rn = 1: Filters the results to include only the most recent order for each customer, which is the one with rn = 1.

Example Data and Results

Assume the Orders table has the following data:

OrderID	CustomerID	OrderDate
1	101	2024-01-10
2	101	2024-02-15
3	102	2024-01-20
4	103	2024-03-05
5	102	2024-03-10

For this dataset:

• Customer 101’s most recent order is with OrderID 2 (2024-02-15).
• Customer 102’s most recent order is with OrderID 5 (2024-03-10).
• Customer 103’s most recent order is with OrderID 4 (2024-03-05).

The result of the query would be:

OrderID	CustomerID	OrderDate
2	101	2024-02-15
5	102	2024-03-10
4	103	2024-03-05

This result shows the most recent order for each customer.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to get the most recent order for each customer.