May 5, 2011 at 7:47 pm
I have table1 with unique logid and table2 with multiple logid and timestamp.
If do the left join, it will return multiple records:
select *
from table1
left join table2 on table1.logid = table2.logid
--will return more rows than table1 and table2.
I need only the one row per logid for table1 with max(timestamp) on table2. How do I achieve that? I also have several other tables to join the table1 in the same query.
Thanks for the help!
May 5, 2011 at 8:02 pm
SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY logid ORDER BY date DESC) AS TieBreaker FROM dbo.table2) dt WHERE dt.TieBreaker = 1
May 5, 2011 at 8:15 pm
SELECT ...
FROM table1 t1
CROSS APPLY (SELECT max(timestamp) As t2MaxTimestamp FROM table2 t WHERE t.logid = t1.logid) t2
CROSS APPLY (SELECT max(timestamp) As t3MaxTimestamp FROM table3 t WHERE t.logid = t1.logid) t3
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 5, 2011 at 8:16 pm
how about this ::
Select * from table1 A(nolock)
left join (select logid,max(timestamp) as Ttime from table2(nolock) group by logid ) B
on A.logid=B.logid
May 5, 2011 at 9:50 pm
Thank you all!
The subquery is a great and simple approach!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply