join one-to-many but return latest one

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

Something like this

select t1.ID, t1.Date1,t1.Value1,t1.Value2,t2.Value3 from table1 as t1

inner join

(

select id,value3 from table2 where date1=(select max(date1) from table2 where id=t1.id)

) as t2

on t1.id=t2.id

Madhivanan

Failing to plan is Planning to fail

weslleywang-616432 SSC Rookie Points: 35 More actions · Answer 2

SSC Rookie

Points: 35

December 11, 2008 at 8:10 am

Thank you, it works like charming

Wes

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

You might want to try using TOP(1) with an ORDER BY. If you're referencing an index, the TOP (1) is pretty consistently faster than the MAX.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

weslleywang (12/11/2008)
Thank you, it works like charming
Wes

You are welcome 🙂

Madhivanan

Failing to plan is Planning to fail