subquery - 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 2000 Forums

Transact-SQL (2000)

subquery

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-10-07 : 08:45:52

hi
i have a table like that (simplifieD)
ID---Name---Comment---Date
1---John---a---01.01.2000
2---John---b---01.01.2001
3---Jack---c---02.02.2004
4---Jack---d---02.02.2005

i want to select the Names that have closest date to now.
i want a result like this.
ID---Name---Comment---Date
2---John---b---01.01.2001
4---Jack---d---02.02.2005

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-07 : 08:59:00

[code]
select *
from table t
inner join
(
select ID, max(Date) as max_date
from table
group by ID
) l
on t.ID = l.ID
and t.Date = l.max_date
[/code]

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-10-07 : 09:09:17

Select * from table t where Date=(
Select Max(Date) from table
where ID= t.ID)

i tried this but it doesnt work too
your query didint worked and.

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-07 : 09:16:44

quote:
Originally posted by bilencekic

Select * from table t where Date=(
Select Max(Date) from table
where ID= t.ID)

i tried this but it doesnt work too
your query didint worked and.

MS BLESS US

Ooops sorry



select *
from   table t
       inner join
       (
           select Name, max(Date) as max_date
           from   table
           group by Name
       ) l
       on  t.Name=  l.Name
       and t.Date = l.max_date

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-07 : 09:22:10

quote:
Originally posted by bilencekic

Select * from table t where Date=(
Select Max(Date) from table
where ID= t.ID)

i tried this but it doesnt work too
your query didint worked and.

MS BLESS US


Select * 
from   table t 
where  Date = (Select Max(Date) from table where Name = t.Name)

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-10-07 : 09:23:06

saol birader =)
(thx very much.)

MS BLESS US

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources