
Question:
Lets say I have a table e.g
Request No. Type Status --------------------------- 1 New Renewed
and then another table
Action ID Request No LastUpdated ------------------------------------ 1 1 06-10-2010 2 1 07-14-2010 3 1 09-30-2010
How can I join the second table with the first table but only get the latest record from the second table(e.g Last Updated DESC)
Solution:1
SELECT T1.RequestNo , T1.Type , T1.Status, T2.ActionId , T2.LastUpdated FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.RequestNo = T2.RequestNo WHERE NOT EXISTS (SELECT * FROM TABLE2 T2B WHERE T2B.RequestNo = T2.RequestNo AND T2B.LastUpdated > T2.LastUpdated )
Solution:2
Using aggregates:
SELECT r.*, re.* FROM REQUESTS r JOIN REQUEST_EVENTS re ON re.request_no = r.request_no JOIN (SELECT t.request_no, MAX(t.lastupdated) AS latest FROM REQUEST_EVENTS t GROUP BY t.request_no) x ON x.request_no = re.request_no AND x.latest = re.lastupdated
Using LEFT JOIN & NOT EXISTS:
SELECT r.*, re.* FROM REQUESTS r JOIN REQUEST_EVENTS re ON re.request_no = r.request_no WHERE NOT EXISTS(SELECT NULL FROM REQUEST_EVENTS re2 WHERE re2.request_no = r2.request_no AND re2.LastUpdated > re.LastUpdated)
Solution:3
SELECT * FROM REQUEST, ACTION WHERE REQUEST.REQUESTNO = ACTION.REQUESTNO --Joining here AND ACTION.LastUpdated = (SELECT MAX(LastUpdated) FROM ACTION WHERE REQUEST.REQUESTNO = ACTION.REQUESTNO);
A sub-query is used to get the last updated record's date and matches against itself to prevent the other records being joined.
Granted, depending on how precise the LastUpdated
field is, it can have problems with two records being updated on the same date, but that is a problem encountered in any other implementation, so the precision would have to be increased or some other logic would have to be in place or another distinguishing characteristic to prevent multiple rows being returned.
Solution:4
SELECT r.RequestNo, r.Type, r.Status, a.ActionID, MAX(a.LastUpdated) FROM Request r INNER JOIN Action a ON r.RequestNo = a.RequestNo GROUP BY r.RequestNo, r.Type, r.Status, a.ActionID
Solution:5
We can use the operation Top 1 with ORDER BY clause. For instance, if your tables are RequestTable(ID,Type,Status) and ActionTable(ActionID,RequestID,LastUpdated), the query will be like this:
Select Top 1 rq.ID, rq.Status, at.ActionID From RequestTable as rq JOIN ActionTable as at ON rq.ID = at.RequestID Order by at.LastUpdated DESC
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon