max date question - 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)

max date question

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-05-05 : 13:22:26

I have 2 tables. A master and detail . I wand to end up with a max bDate, and t2Val for bob. So..

1 bob 5/15/2005 boat

table 1

t1ID
t1Name

1 bob
2 gary

Table 2
t2ID
t1ID
bDate
t2Val

1 1 2/11/2005 car
2 1 5/15/2005 boat
3 2 3/23/2005 van
4 1 1/01/2005 cart

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-05 : 14:52:26

Select bdate ,t2val
from table1 innerjoin table2 on table1.t1id = table2.t1id
where t1name = 'bob' and bdate in (Select max(bdate) from table1 innerjoin table2 on table1.t1id = table2.t1id where t1name = 'bob' )

Jim
Users <> Logic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-05 : 16:39:29

[code]declare @t1 table(t1id int, t1name varchar(15))
declare @t2 table(t2id int, t1id int, bdate datetime, t2val varchar(15))

insert into @t1
select 1, 'bob' union select 2, 'gary'
insert into @t2
select 1, 1, '2/11/2005', 'car' union
select 2, 1, '5/15/2005', 'boat' union
select 3, 2, '3/23/2005', 'van' union
select 4, 1, '1/01/2005', 'cart'

select top 1
t1.t1id,
t1.t1name,
t2.bdate,
t2.t2val
from @t1 t1 inner join @t2 t2 on t1.t1id = t2.t1id
order by 3 desc[/code]

... testing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources