Most Recent in a List

December 6, 2007

Anyone ever been asked to pull a report of the most recent transaction for every customer?  Sounds pretty easy at first, but it turns out to be tricky.

My scenario wasn’t exactly that, but it was the same problem.

You can see right away that we need to perform some sort of a GROUP BY on CustomerID or whatever the business key is for customer.  What I came up with was a two part solution.  I was really under the gun so I didn’t have a chance to look for a more elegant solution – so you know how that goes.  Here is what I did:

I split the query up into two parts.  The first part being a select statement that selected the CustomerID and the MAX of the TransactionDate while having a GROUP BY clause on CustomerID.  Then I INNER JOINed to this statement with a statement like this:

SELECT * FROM Transaction t
SELECT CustomerID, MAX(TransactionDate) MaxTransactionDate
FROM Transaction
) ss
on t.CustomerID = ss.CustomerID
and t.TransactionDate = MaxTransactionDate

I suppose that this can call back two transactions for one customer if they have the same TransactionDate.  In my case, this was ok.

Does anyone see another more elegant way to do this with one statement instead of two?  Could you use a CTE?