I have seen a lot of questions regarding how to obtain the most current row, for a given grouping. The type of query is very typical for a one-to-many relationship. In our example, we will be creating an Orders table and our objective is to find the most recent order for each customer. Let’s start by creating our table structure.
USE [tempdb]
GO
IF OBJECT_ID('tempdb.dbo.Customers') IS NOT NULL
BEGIN
DROP TABLE dbo.Customers;
END
GO
CREATE TABLE dbo.Customers(
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
EmpSalesID INT,
FName VARCHAR(25),
LName VARCHAR(25),
Phone CHAR(12)
);
INSERT INTO Customers VALUES (1,'Adam','Haines','555-555-5555');
INSERT INTO Customers VALUES (1,'John','Deere','555-555-7777');
INSERT INTO Customers VALUES (2,'Allision','Smith','555-555-8888');
IF OBJECT_ID('tempdb.dbo.Orders') IS NOT NULL
BEGIN
DROP TABLE dbo.Orders;
END
GO
CREATE TABLE dbo.Orders(
OrderId INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT,
Order_Amt NUMERIC(9,2),
Order_Dt DATETIME
);
INSERT INTO dbo.Orders VALUES (1,199.50,'2009-08-30');
INSERT INTO dbo.Orders VALUES (1,49.99,'2009-09-05');
INSERT INTO dbo.Orders VALUES (2,2500.00,'2009-09-08');
INSERT INTO dbo.Orders VALUES (3,1.00,'2009-08-08');
INSERT INTO dbo.Orders VALUES (3,2.00,'2009-09-07');
INSERT INTO dbo.Orders VALUES (3,3.00,'2009-09-08');
Now that we have our table structure, we can start writing our query. As you can see, we have a one-to-many relationship between customer and orders. When we join our Customers table to the Orders table we will see multiple rows returned, but our objective is to return the most recent row for each customer. I will illustrate two different methods, for obtaining this data. The first method uses a CTE (Common Table Expression). The key to this method is the row_number function, as this can be used to partition and sort the data to our liking. In my example, I partition by the customer id and ordered by the order_dt DESC because I want to return the newest order placed by each customer. This gives the newest record a sequence value of 1 per CustomerID. I can then filter the outermost CTE for all sequences that are equal to 1, or the most current.
--SQL 2005+
--create cte to select the data we want
;WITH Orders (seq,CustomerID,FName,LName,OrderId,Order_Amt,Order_Dt)
AS
(
SELECT --use the row_number function to get the newest record
ROW_NUMBER() OVER(PARTITION BY c.[CustomerID] ORDER BY Order_Dt DESC) AS seq,
c.CustomerID,c.FName,c.LName,o.OrderId,o.Order_Amt,o.Order_Dt
FROM dbo.Customers c
INNER JOIN dbo.Orders o
ON c.[CustomerID] = o.[CustomerID]
)
SELECT CustomerID,FName,LName,OrderId,Order_Amt,Order_Dt
FROM Orders
WHERE seq = 1 --filter for the newest record only
ORDER BY CustomerID
Next, I will show you a method that works on SQL Server versions less than 2005. This method works on the same principal but uses the max aggregate to get the most current order_dt per customerId. We can then join this order_dt back to the orders table to get the most recent order row data.
--SQL 2000
SELECT o.CustomerID,c.FName,c.LName,o.OrderId,o.Order_Amt,o.Order_Dt
FROM dbo.Customers c
INNER JOIN dbo.Orders o
ON c.[CustomerID] = o.[CustomerID]
INNER JOIN(
SELECT CustomerId, MAX(Order_Dt) AS Order_Dt
FROM dbo.Orders
GROUP BY [CustomerId]
) AS Newest_Order
ON O.CustomerID = Newest_Order.CustomerId
AND o.[Order_Dt] = Newest_Order.[Order_Dt]
ORDER BY o.[OrderId]
There you have it!!! I have provided two simplistic methods to get the most current row. This method is very straight forward and very easy to implement.
Happy Coding.
