Most Recent in a List

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
INNER JOIN
(
SELECT CustomerID, MAX(TransactionDate) MaxTransactionDate
FROM Transaction
GROUP BY CustomerID
) 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?

Advertisements

2 Responses to Most Recent in a List

  1. Jon says:

    Here is a better way that doesn’t require processing the table twice and it doesn’t use a group by.

    create table transaction_table
    (customer_id int, transaction_date smalldatetime)

    insert into transaction_table values (1, getdate())
    insert into transaction_table values (1, getdate()+ 1)
    insert into transaction_table values (1, getdate()+ 2)
    insert into transaction_table values (1, getdate()+ 3)

    insert into transaction_table values (2, getdate())
    insert into transaction_table values (2, getdate()+ 1)
    insert into transaction_table values (2, getdate()+ 2)
    insert into transaction_table values (2, getdate()+ 3)
    insert into transaction_table values (2, getdate()+ 4)
    insert into transaction_table values (2, getdate()+ 5)

    select *
    from (select t.*,
    row_number() over (partition by customer_id order by transaction_date desc) as rank_1
    from transaction_table t) sub
    where sub.rank_1 = 1

  2. Mark Garner says:

    Awesome. Thanks for the query.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: