Select top 2 rows from records, grouped/partitioned by Unique ID
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
Shows the records with their new ROW_NUMBERs added and partitioned
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 🙂