SQL Query to take out the row for each id with th

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query to take out the row for each id with th

Author

Topic

nishanthnair
Starting Member

6 Posts

Posted - 2009-05-02 : 07:25:30

I have a table like this
The LastEvent column is DateTime type.

Id EventName LastEvent
1 Name1 4/29/09
1 Name2 4/22/09
1 Name3 3/29/09
2 Name12 1/2/09
2 Name13 4/25/09
2 Name14 2/29/09
3 Name32 1/2/08
3 Name33 3/11/09
3 Name34 4/29/09

Now here, I need a SQL Query to take out the row for each id with the latest event.

So the out put will be
Id EventName LastEvent

1 Name1 4/29/09
2 Name13 4/25/09
3 Name34 4/29/09

As you can see, for each id, these rows are the latest event(MAX(LastEvent)).

It will be helpful if any one of you experts can help me with the query to get this output.

Thanks,
Nishanth

Cheers,
Nishanth

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-02 : 07:41:03

[code]
select
t1.id,
t1.eventName,
t1.lastEvent
from table t1
join
(select id, max(lastEvent) as lastEvent from from table group by id) t2
on t1.id=t2.id and t1.lastEvent=t2.lastEvent
[/code]

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

nishanthnair
Starting Member

6 Posts

Posted - 2009-05-02 : 10:08:23

Thanks a lot webfred. That self join did the trick.

Cheers,
Nishanth

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-05-02 : 15:36:26

[code]
select id,eventname,lastevent
from
(Select *,ROW_NUMBER() Over (Partition by id order by lastevent desc) as Seq
from table)Z
Where Z.seq =1
[/code]

nishanthnair
Starting Member

6 Posts

Posted - 2009-05-03 : 08:03:47

Sodeep,
Thanks a lot for your query... That is more helpful as the first query will return all the rows when the lastevent field is the same for all ids.

Cheers,
Nishanth

nishanthnair
Starting Member

6 Posts

Posted - 2009-05-03 : 10:05:31

I have one more question. In the table I have one more column EventCount. Now in the unique row which we selected
I need a SUM(COUNT) of all the other rows with the same id. Can any one of you help me with this query please?

Cheers,
Nishanth

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-03 : 13:33:37

[code]
declare @Sample table(id int,EventName varchar(255), LastEvent datetime, EventCount int)
insert @Sample
select 1, 'Name1','20090429',1 union all
select 1, 'Name2','20090429',2 union all
select 1, 'Name3','20090329',3 union all
select 2, 'Name12','20090102',12 union all
select 2, 'Name13','20090425',13 union all
select 2, 'Name14','20090228',14 union all
select 3, 'Name32','20080102',32 union all
select 3, 'Name33','20090311',33 union all
select 3, 'Name34','20090429',34

--select * from @Sample

select
Z.id,
Z.eventname,
Z.lastevent,
Z2.SumEventCount
from
(Select *,
ROW_NUMBER() Over (Partition by id order by lastevent desc) as Seq
from @Sample)Z
join
(select
id,
sum(EventCount) as SumEventCount,
ROW_NUMBER() Over (Partition by id order by max(lastevent) desc) as Seq
from @Sample group by id)Z2
on Z.id=Z2.id
Where Z.seq =1
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources