Sample Data
DECLARE @TT table(CustID int, ProductID int, OrderDate datetime, Spending decimal(10,3))
INSERT @TT
SELECT 1133, 100 , '04/28/2009', 5.03 UNION ALL
SELECT 1431, 103 , '04/28/2009', 19.02 UNION ALL
SELECT 1431, 105 , '04/28/2009', 15.00 UNION ALL
SELECT 1133, 100 , '04/29/2009', 13.40 UNION ALL
SELECT 1142, 105 , '04/29/2008', 14.60 UNION ALL
SELECT 1142, 103 , '04/29/2008', 11.70 UNION ALL
SELECT 1133, 100 , '04/29/2008', 18.60
Query
-- Find Most Recent Order for each Customer
SELECT Custid, Spending, OrderDate FROM
(
SELECT CustID, Spending, OrderDate, ROW_NUMBER() OVER
(PARTITION by CustID ORDER BY OrderDate) AS custGrp
FROM @TT
) AS Tot
WHERE custGrp = 1
Results
Custid Spending OrderDate
1133 18.600 2008-04-29 00:00:00.000
1142 14.600 2008-04-29 00:00:00.000
1431 19.020 2009-04-28 00:00:00.000
Hi Suprotim Agarwal,
ReplyDeleteThe query you posted retrieves the first order placed by the customer. In order to retrieve the most recent you should add the keyword DESC to the order by in the ROW_NUMBER.
something like:
ORDER BY OrderDate DESC
Cheers,
Jose
Thanks Jose. That makes a lot of sense!
ReplyDelete