Need 2 return the record with the maximum 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)

Need 2 return the record with the maximum date

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2009-02-10 : 14:55:23

Hi I have a query that can return multiple values for the columns EffectiveDate and level. These multiple records are for the same values of the oher columns: IDpersonne, Name, LastName...etc

How do I tell the query when we have the same values of IDpersonne, Name, LastName to return only the record whose whose EffectiveDate is equal to the max(EffectiveDate)

Here is what I mean:

My query: Select IDpersonne, Name, LastName, Level, EffectiveDate can return for example the values:

1, Name1, Last1, Level2, 2009/07/01
1, Name1, Last1, Level5, 2008/07/15
1, Name1, Last1, Level3, 2015/09/27

How do I write the query so It returns the record whose EffectiveDate is the biggest meaning this one:

1, Name1, Last1, Level3, 2015/09/27

p.s: I don't have problem returning the max date, but then I don't want to lose the corresponding level coulmn. Please also notice that I gave here just a sample of the query, the real query I have is much bigger and complex than this one, but I just need the idea to implement what I need.

Thanks :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 15:00:47

This type of question is answered several times per day.
INNER JOIN a derived table holding max date per group.

E 12°55'05.63"
N 56°04'39.26"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-10 : 15:02:11

SELECT dt.IDpersonne, dt.Name, dt.LastName, yt.Level, dt.EffectiveDate
FROM YourTable yt
INNER JOIN (
Select IDpersonne, Name, LastName, MAX(EffectiveDate) AS EffectiveDate
FROM YourTable
GROUP BY IDpersonne, Name, LastName) dt
ON yt.IDpersonne = dt.IDpersonne AND yt.Name = dt.Name AND yt.LastName = dt.LastName AND yt.EffectiveDate = dt.EffectiveDate

If you were using SQL Server 2005, you could make use of the ROW_NUMBER() function so you'd see better performance since you'd only need to hit the table once.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources