Issue with Grouping - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Issue with Grouping

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-09-19 : 09:44:34

Hi

I have table

ID timestamp Comment-text
1 2013-09-19 10:00 Test
1 2013-09-19 10:02 test2
1 2013-09-19 10:03 test3
2 2013-09-19 10:04 test
2 2013-09-19 10:05 test2
3 2013-09-19 10:01 test3

I would like to have for every ID the latest(based on date) 'Comment-text'

so for ID's - 1- test3 , for 2 test2

In addition I would also like to display this status but using group by I got error message because this 'comment-text' field is text filed

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-19 : 09:51:23

select [ID], [Comment-text] from
(select row_number() over (partition by [ID] order by [timestamp] DESC) as rn,* from YourTable) as dt
where rn=1

Too old to Rock'n'Roll too young to die.

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 19:31:57

select t.*
from
(
select id, max([timestamp]) mx
from table_name
group by id
)d
inner join table_name t
on d.id = t.id
and d.mx = t.[timestamp];

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources