Select record with max date - 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

General SQL Server Forums

New to SQL Server Programming

Select record with max date

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-09-18 : 13:03:47

Hello

I have a table with columns

Key
Date
Value

For each Key I can have different values for each date
There is only ONE record by date

How can I get the last date record for each key ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-18 : 13:08:01

[code]
select * from
(
select row_number() over (partition by Key oder by Date desc) as rnum,
*
from your_table
) as dt
where rnum = 1
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-19 : 06:47:46

post some sample data :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-19 : 06:50:09

quote:
Originally posted by webfred

select * from
(
select row_number() over (partition by Key order by Date desc) as rnum,
*
from your_table
) as dt
where rnum = 1

No, you're never too old to Yak'n'Roll if you're too young to die.

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-19 : 06:53:13

kh you EAGLE EYE

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-19 : 07:37:52

quote:
Originally posted by webfred

kh you EAGLE EYE

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks to my new pair of glasses

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-19 : 11:26:36

if on SQl 2000


SELECT t.*
FROM Table t
INNER JOIN (SELECT Key,MAX([date]) AS Latest
            FROM Table
            GROUP BY Key) t1
ON t1.Key=t.Key
AND t1.Latest = t.[date]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-21 : 11:39:36

or

select * from table as t where date=(select max(date) from table where key=t.key)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources