April 11, 2014 at 1:09 pm
Hi,
Let's say I have a query like:
SELECT * FROM Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.ID
INNER JOIN Table3 T3 ON T1.ID = T3.ID
LEFT JOIN Table4 T4 ON T3.Key1 = T4.Key2
WHERE ...
GROUP BY ...
In Table2, there is a datetime column. I want the JOIN to be only on the most recent datetime and forget about the others. How can I do this??
thanks a lot for your time and help!
Dominic
April 11, 2014 at 1:15 pm
Sure you can do it. You'll need to join on a subquery that gets the max datetime value
April 11, 2014 at 1:19 pm
Dominic Gagné (4/11/2014)
Hi,Let's say I have a query like:
SELECT * FROM Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.ID
INNER JOIN Table3 T3 ON T1.ID = T3.ID
LEFT JOIN Table4 T4 ON T3.Key1 = T4.Key2
WHERE ...
GROUP BY ...
In Table2, there is a datetime column. I want the JOIN to be only on the most recent datetime and forget about the others. How can I do this??
thanks a lot for your time and help!
Dominic
Something like this might do it:
SELECT * FROM Table1 T1
--INNER JOIN Table2 T2 ON T1.ID = T2.ID
CROSS APPLY (
SELECT top 1 * from Table2 T2
where T1.ID = T2.ID
order by t2.<datetime column> desc
) T2
INNER JOIN Table3 T3 ON T1.ID = T3.ID
LEFT JOIN Table4 T4 ON T3.Key1 = T4.Key2
WHERE ...
GROUP BY ...
April 11, 2014 at 1:22 pm
oh yeah!!! how in heaven I didn't thought about it at first!!!
thanks a lot!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply