January 12, 2009 at 10:38 am
Hey all,
I recently had this problem thrown at me to find the people three most recent activity records.
and i need the data like this:
[font="Courier New"]id mostrecentactivity 2ndmostrecent 3rdmostrecent
---- -------------------- --------------- ----------------
1 10/1/2008 9/15/2008 9/1/2008
2 3/1/2008 2/1/2008 1/1/2006[/font]
Now I've had this task before, in sql server 2000, and have solved it using a series of queries that basically went something like:
select id, max(date)
into #secondmostrecent
from table t1
inner join #mostrecnt t2
on t1.id = t2.id
where t1.date < t2.date
or maybe even (ugh) a cursor.
Now, sql server 2005 i found I was able to use row_number() and pivot to find the data much faster and easier. so now it looks like!
select
id
[1] as mostrecentactivity ,
[2] as 2ndmostrecent ,
[3] as 3rdmostrecent
from (
select
row_number() over (partition by id order by id, activitydate desc) rownum,
activitydate,
id
from table
) t1
pivot
(
max(activitydate)
for
--this pivot clause will make the results 1 thru 3 become columns to be queried later
rownum in([1],[2],[3])
) as pvt
this was great solution, b/c its fast and by using this combination i can effectively flip any table sideways!
January 26, 2009 at 3:13 am
This is a very common question in newsgroups/forums, and you've provided a very intuitive example.
Have you considered posting this in a blog? It would certainly be useful for quite a number of users.
Don't let this fine example of T-SQL programming vanish in the depths of this highly frequented forum. 😉
Kudos.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
January 26, 2009 at 7:31 am
Thanks! I had considedered posting this in a blog but don't know where I could post it. Do you have any suggestions of where I could place this script?
January 26, 2009 at 7:37 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply