Getting most current 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

SQL Server 2008 Forums

Transact-SQL (2008)

Getting most current date

Author

Topic

loiter
Starting Member

7 Posts

Posted - 2010-10-20 : 10:24:32

Hello,
I have a table of student information, every time data is changed there is a new row added with a date stamp. How do I select the most current record for each student from multiple rows?

thanks
J

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 10:52:59

Please give DDL and sample data.
I think it is possible using row_number() but I need more info...

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

loiter
Starting Member

7 Posts

Posted - 2010-10-20 : 12:36:10

Sure:

#scores
id .... score ... date
1 .... 100 ... 1/1/2000
2 .... 107 ... 1/1/2000
3 .... 100 ... 1/1/2000
1 .... 099 ... 1/1/2001
2 .... 110 ... 1/1/2001

I want to get the record for each student with the most recent date.

thanks
J

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 15:01:11

select id,score,[date] from
(
select
row_number() over (partition by id order by [date] DESC) as rownum,
id,
score,
[date]
from #scores
) as dt
where rownum=1

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

loiter
Starting Member

7 Posts

Posted - 2010-10-20 : 15:11:36

Thank you, I will give it a go !

J

loiter
Starting Member

7 Posts

Posted - 2010-10-20 : 15:25:17

ahhh, that is it !!! AWESOME !! ... is it possible to put those results in a temp table?

thanks
J

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 15:39:00

Yes - like you would do it always.
The following statement works only if the target temp table not already exists!


select id,score,[date] 

into #yourtempScoreTable

from
(
select
row_number() over (partition by id order by [date] DESC) as rownum,
id,
score,
[date]
from #scores
) as 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