How to get the latest items per grouping? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to get the latest items per grouping?

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-10 : 13:24:24

Hi,

Wondering if I might get some advice on the following. Thanks!

For this result set:
--Number--Name-----Cat----Message-------MssgTime
--001-----Product1-Retail-this is Good--1/10/08 10:00AM
--001-----Product1-Retail-this is OK----1/10/08 10:01AM
--001-----Product1-Retail-this is Bad---1/10/08 10:03AM
--002-----Product2-Wholsl-this is blah--1/10/08 11:01AM
--002-----Product2-Wholsl-this is Sad---1/10/08 10:03AM
--003-----Product3-Commrc-this is it----1/10/08 11:01AM
--003-----Product3-Commrc-this is yuk---1/10/08 11:30AM

What query would get me this:
--001-----Product1-Retail-this is Bad---1/10/08 10:03AM
--002-----Product2-Wholsl-this is blah--1/10/08 11:01AM
--003-----Product3-Commrc-this is yuk---1/10/08 11:30AM

In other words the row with the latest message for each product

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-10 : 13:35:40

Write a query with group by on the Number, Name, Cat columns and a MAX on MssgTime and join it back to the main table on these columns.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-10 : 13:44:03

Thanks, might you be able to provide a sample? I'm not that great at SQL.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-10 : 13:56:00

[code]
Select T1.*
From YourTable T1
JOIN (
select Number, Name, Max(MssgTime) MTime
from YourTable T3
Group by Number, Name
) T2 ON T1.Number= T2.Number AND T1.Name = T2.Name And T1.MssgTime = T2.MTime
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-01-10 : 14:36:19

Thanks so much! From your answer I realised I could do it easier.

I ended up doing this, which seems simpler to me:

select distinct T1.Name, MAX(T1.MTime)
from
(
select Number,Name,Cat,Message, MAX(MssgTime)
from mytable
Group by Number,Name,Cat,Message
) as T1
Group By T1.Name

Thanks again! I love this site!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-10 : 15:59:59

If that is all you need then you can do this:

SELECT Name, MAX(MTime)
FROM mytable
GROUP Name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-11 : 02:28:07

quote:
Originally posted by phrankbooth

Thanks so much! From your answer I realised I could do it easier.

I ended up doing this, which seems simpler to me:

select distinct T1.Name, MAX(T1.MTime)
from
(
select Number,Name,Cat,Message, MAX(MssgTime)
from mytable
Group by Number,Name,Cat,Message
) as T1
Group By T1.Name

Thanks again! I love this site!!

Using Distinct over grouped column is meaningless

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources