J.D. Hodges

The Problem: A client needed a query that selected the two most recent tour dates for each user ID.

This is an easy task for a single user ID because specifying the user ID and an ORDER BY tour_date DESC with a LIMIT 2 clause would get the dates needed. However, when there are a lot of users it gets harder.

For a single date, the MAX function works great but in our instance it will not work because we need the top two records 🙁

The Solution

The SQL Server (2008 or later) ROW_NUMBER() function and PARTITION can do what we need:

SELECT fk_cus_id, purchase_date_last, [Times Toured], tour_date, ROW_NUMBER() OVER (PARTITION BY fk_cus_id ORDER BY tour_date DESC) AS tour_date_number

FROM dbo.XXXXXX

After that it is a simple matter of limiting the tour_date_number to <=2 and you end up with only the top 1 or 2 dates (as available) for each user 🙂