How to Retrieving the Last Record in Each Group in SQL Server

In SQL Server data analysis, the need to retrieve the last record in each group is a common and essential task. Consider a scenario where you are managing a database of customer orders. Each order is associated with a unique order ID and a customer ID, and you want to identify the most recent order placed by each customer. This could be crucial for various business decisions, such as identifying loyal customers, analyzing purchasing patterns, or providing personalized recommendations.

Retrieving the Last Record in Each Group

In SQL Server, retrieving the last record in each group is essential for tasks like identifying the most recent customer orders. This helps analyze patterns, recognize loyal customers, and make informed business decisions.

Understanding the Problem:

Imagine you have a database table named Orders with columns for OrderID, CustomerID, OrderDate, and other relevant details. Each row represents a unique order placed by a customer, and you want to identify the latest order placed by each customer.

USE AdventureWorks2019

CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES
    (1, 101, '2023-01-15'),
    (2, 102, '2023-01-20'),
    (3, 101, '2023-02-05'),
    (4, 102, '2023-02-10'),
    (5, 101, '2023-03-01'),
    (6, 103, '2023-03-05');

SELECT * FROM Orders;

So, we have inserted the sample data into our table, and output of the select query should look like below image.

Figure 1: Sample data inserted into the table

Now we will see how to solve our problem using below two methods

Example of Retrieving the Last Record in Each Group in SQL Server

Example 1: Using ROW_NUMBER() with Common Table Expression (CTE)

The ROW_NUMBER() function assigns a unique sequential integer to each row within a partition of a result set. We can leverage this function to assign row numbers to each record within groups and then filter out the last record in each group.

Query:

;WITH LastRecordCTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowNum
    FROM Orders
)
SELECT *
FROM LastRecordCTE
WHERE RowNum = 1;

Output:

Figure 2: We can see the last record for each customer

Explanation: This query partitions the data by CustomerID and assigns row numbers based on the descending order of OrderDate. The outer query then filters the results to only include rows where the row number is 1, representing the last order for each customer.

Please note that we have used “;” before the WITH CTE syntax, and this is because CTE is always used in a single batch so to create a batch we have used the ; to separate previous queries.

Example 2: Using Subquery

Alternatively, we can achieve the same result using a subquery. We first assign row numbers within groups in a subquery and then filter out the last records in the outer query.

Query:

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowNum
    FROM Orders
) AS Subquery
WHERE RowNum = 1;

Output:

Figure 3: We can see the last record for each customer using subquery

Explanation: This approach calculates row numbers within groups defined by CustomerID and orders the rows by OrderDate in descending order. The outer query then selects only the rows where the row number is 1, indicating the last order for each customer.

Both methods will return the last order for each customer from the Orders table based on the OrderDate. Choose the one that best fits your preference or performance requirements.

Conclusion

Retrieving the last record in each group in SQL is very important and most used task in Database and in this article, we have seen how to tackle it. Note that when we are asked to receive the first record rather than the last one which also makes another scenario, we need to make one simple change either replace descending order with ascending or change the outer where condition to select top 1 order by descending rather than fetching the data based on where condition. Do try this and please add in comment if need any help regarding this article.