Extracing row corresponding to max date

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

Transact-SQL (2000)

Extracing row corresponding to max date

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2008-07-07 : 18:01:44

Hi,
I have a prototype table named tblTemp that has the following rows:

Name EditedProject Date
Sam 100A 04/03/08
Mary 200C 02/01/08
Tim 101P 03/04/08
Tim 101P 05/04/08
Robin 109T 06/11/08
Sam 100A 05/21/08
Sam 100A 06/25/08

Now I have to list the rows that corresponds to the maximum editing date. Thus the desired resultset will be as follows:

Name EditedProject Date

Mary 200C 02/01/08
Tim 101P 05/04/08
Robin 109T 06/11/08
Sam 100A 06/25/08

I have no idea how to handle the above. Any help is appreciated. Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-08 : 02:40:18

Max date by name or by edited or both?

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 06:38:08

[code]SELECT t.*
FROM tblTemp t
INNER JOIN (SELECT Name,MAX(Date) AS MaxDate
FROM tblTemp
GROUP BY Name)tmp
ON tmp.Name=t.Name
AND tmp.MaxDate=t.Date[/code]

gamaz
Posting Yak Master

104 Posts

Posted - 2008-07-08 : 11:19:38

Thanks Vaisakh for the generous help. It worked perfect. Gail sorry for not been explicitly clear in my statement. I meant to say to extract row corresponding to the latest date. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources