Selecting entire record with latest 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

SQL Server Development (2000)

Selecting entire record with latest date

Author

Topic

outspaced
Starting Member

21 Posts

Posted - 2006-12-07 : 11:46:53

Hi,

I have several records with the same ID, and I'd like to select the record where the id matches my id with the latest date.

eg
name animal_id date
boris 2 01/02/03
cedric 2 01/01/01
mustapha 2 01/03/02
gunther 4 10/05/05

... I'd like to select the record for boris.

Thanks,
Alex

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 11:53:35

[code]Select *
from tbl t1
where id = @id
and [date] = (Select max([Date]) from tbl t2 where t1.id = t2.id)[/code]

Harsh Athalye
India.
"Nothing is Impossible"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-07 : 12:21:00

An alternative that may give a better plan ...

select *
from tbl t1
where id = @id
and not exists (
select 1
from tbl t2
where t1.id = t2.id and
t2.date > t1.date)

Jay
to here knows when

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources