How to get ID of the Max Date based on year

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)

How to get ID of the Max Date based on year

Author

Topic

jyothsna18
Starting Member

1 Post

Posted - 2010-04-14 : 11:12:31

I have a Table with the below structure.
ID SurveyDate FY
100 8/25/2009 2009
200 8/26/2009 2009
300 8/29/2009 2009
400 9/1/2010 2010
500 9/30/2010 2010
i want ID for each Year Max Date. How to get that easily? I need ID only Because this ID i want to use as part of the Sub query.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-14 : 11:33:16

Try this:

select id from
(
select
id,
row_number() over (partition by FY order by SurveyDate DESC) as rownum
from Your_Table
)dt
where rownum=1

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