February 18, 2011 at 8:47 am
I have a table that looks something like this:
EventID Ev_Cat Ev_Date Ev_status
1 2 2011-01-01 4
2 5 2011-01-07 1
3 3 2011-01-10 2
4 7 2011-01-10 4
5 3 2011-01-15 1
6 2 2011-01-18 2
7 5 2011-01-22 4
8 5 2011-01-27 3
I am trying to determine the last Ev_status for each Ev_Cat so that the results should look as:
Ev_Cat Ev_Date Ev_status
2 2011-01-18 2
3 2011-01-15 1
5 2011-01-27 3
7 2011-01-10 4
Does anyone know what the SQL statement should look like?
Any help would be much appreciated
February 18, 2011 at 8:57 am
You can use the ranking functions and a CTE for this in SQL 2005 or later.
Will look like:
;with Ranked as
(select Row_Number() over (partition by Ev_Cat order by Ev_Date desc) as Row,
Ev_Cat, Ev_Date, Ev_status
from dbo.MyTable)
select Ev_Cat, Ev_Date, Ev_status
from Ranked
where Row = 1
order by Ev_Cat;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 18, 2011 at 10:14 am
Thank you very much. That was most helpful 🙂
February 18, 2011 at 11:25 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply