How to return all records with the MAX date in only ONE query

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)

How to return all records with the MAX date in only ONE query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-03 : 09:57:05

John writes "This one has stumped me for a long time, and I've asked other developers who said it couldn't be done. But I think there has to be a way. Say you have the following table:

ID Date    Name   City
1  2/3/02  Joe    Detroit
2  2/4/02  Joe    Ovid
3  1/8/98  Sue    Moscow
4  1/9/99  Sue    Moscow

How, in ONE query, can you return only the Date, Name, and City records with the maximum date? In other words, you want to return:

ID Date    Name   City
2  2/4/02  Joe    Ovid
4  1/9/99  Sue    Moscow

If you do MAX(Date) and GROUP BY Name, you need another query to get City. If you add City to the GROUP BY, it will return two records for Joe, instead of one."

Nazim
A custom title

1408 Posts

Posted - 2002-04-03 : 10:17:10

select * from table t
inner join
(select name,max(date) as DT from
table group by name) t1
on t.name=t1.name and t.date=t1.dt

HTH

--------------------------------------------------------------

Jay99

468 Posts

Posted - 2002-04-03 : 10:19:18

They are developers . . . what do you expect :)


drop table #jay
go
create table #jay
(
    id int,
    date datetime,
    name  varchar(10),
    city varchar(10)
)
go
insert #jay values (1,'2/3/02','Joe','Detroit')
insert #jay values (2,'2/4/02','Joe','Ovid')
insert #jay values (3,'1/8/98','Sue','Moscow')
insert #jay values (4,'1/9/99','Sue','Mosocow')
go

select
    j.id,
    dt.maxdate,
    dt.name,
    j.city
from
    #jay j
    inner join (
        select
            name,
            max(date) as maxdate
        from
            #jay
        group by name ) dt
    on (j.name = dt.name and
        j.date = dt.maxdate)

Jay
<O>

EDIT: . . . great minds think alike, I guess . . .

Edited by - Jay99 on 04/03/2002 10:20:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources