December 11, 2008 at 7:22 am
Hi,
I have 2 table with one-to-many relationship
Table1
ID Value1 Value2 ....
1 a b
2 a1 b1
3 a2 b2
....
Table2
ID Date1 Value3 ...
1 09/Nov/2008 a
1 09/Jul/2008 b
2 09/May/2008 c
3 09/Jan/2008 d
2 07/Jun/2008 e
....
How I can get the result like
ID Date1 Value1 Value2 Value3....
1 09/Nov/2008 a b a
2 07/Jun/2008 a1 b1 e
3 09/Jan/2008 a2 b2 d
...
Thank you,
Wes
December 11, 2008 at 7:32 am
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
Failing to plan is Planning to fail
December 11, 2008 at 8:10 am
Thank you, it works like charming
Wes
December 11, 2008 at 8:17 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 16, 2008 at 3:49 am
weslleywang (12/11/2008)
Thank you, it works like charmingWes
You are welcome 🙂
Failing to plan is Planning to fail
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply