Most Recent Record in a Left Join

Most recent record in a left join

Try this:

SELECT a.State, count(c.CustomerID)
FROM Product p
INNER JOIN Customer c ON c.CustomerID = p.CustomerID
LEFT JOIN Address a ON a.CustomerID = c.CustomerID 
      AND a.AddressID = 
        (
           SELECT MAX(AddressID) 
           FROM Address z 
           WHERE z.CustomerID = a.CustomerID
        )
WHERE p.ProductID = 101
GROUP BY a.State

Access - LEFT JOIN Query Most Recent Record Only

You can get the most recent row first before joining:

SELECT o.OpportunityID, n.NotesDate, n.NotesID
FROM Opportunity as o LEFT JOIN
     (SELECT n.*
      FROM tblNotes as n
      WHERE n.NotesDate = (SELECT MAX(n2.NotesDate)
                           FROM tblNotes as n2
                           WHERE n2.OpportunityID = n.OpportunityID
                          )
     ) as n
     ON o.OpportunityID = n.opportunityid 
ORDER BY o.OpportunityID;

MYSQL: select latest record only (on left join table)

You'll need some subquery's for that:

SELECT
    a.*, b.*
FROM
    table1 a
LEFT JOIN
    (SELECT c.id, d.contacted_for, c.time
     FROM
         (SELECT
            id,
            MAX(time) time
         FROM
             table2
         GROUP BY id
         ) c
     JOIN
         table2 d
         ON c.id = d.id AND d.time = c.time
     ) b
     ON a.id = b.id

SQL - Left Join return latest record

For Teradata, you can use Qualify to constrain on ODAP functions, ROW_NUMBER() in this case.. Can't fully answer your question without knowing more about your data. But basically, add to your query

QUALIFY ROW_NUMBER() OVER (PARTITION BY <YOUR KEY COLUMN(S) ORDER BY  T4.EFF_END_DT DESC) = 1

That will give you the most recent row (based on your column eff_end_dt) for each "key".

SQL left join with latest record

I like using TOP 1 WITH TIES in this case:

SELECT TOP 1 WITH TIES c.CustID, i.InvDate, i.InvNumber
FROM #Customer1 c
LEFT JOIN #Invoices i ON c.CustID = i.CustID
ORDER BY ROW_NUMBER() OVER (PARTITION BY c.CustID ORDER BY i.InvDate DESC);

Demo

The top 1 trick here is to order by row number, assigning a sequence to each customer, with the sequence descending by invoice date. Then, this approach retains just the most recent invoice record for each customer.

Get latest record per timestamp in a left join query

The simplest way is probably a correlated subquery:

SELECT o.id AS orderid, c.name AS CLIENT, u.name AS rep,
       (SELECT s.status
        FROM status s
        WHERE o.id = s.order_id
        ORDER BY s.date DESC
        LIMIT 1
       ) latest_status
FROM Orders o LEFT JOIN
     Customers c
     ON o.customer_id = c.id LEFT JOIN
     Users u
     ON o.rep_id = u.id ;

Join tables based on most recent date for each record

An OUTER APPLY is like the LEFT JOIN that you tried, but it allows you to join to an inline view AND it allows you to refer to columns from previously joined tables in the WHERE clause of that inline view.

Using OUTER APPLY you can use the WHERE clause to find all the backups that occurred on or before each date, use the ORDER BY clause to sort them by backup date latest-to-earliest, and then use the FETCH FIRST clause to just get the first one in the sorted list (i.e., the latest backup).

SELECT d.*, b.DATE most_recent_backup
FROM   my_dates d
OUTER APPLY ( SELECT b.date
              FROM backups b 
              WHERE b.date <= d.date 
              ORDER BY b.date DESC
              FETCH FIRST 1 ROW ONLY ) b

You can also do this with NOT EXISTS if you aren't on a version of Oracle that supports OUTER APPLY. Something like this:

SELECT d.*, b.DATE most_recent_backup
FROM   my_dates d
LEFT JOIN backups b ON b.date <= d.date
WHERE ( 
    -- Either the backup date is NULL (this happens if the LEFT JOIN
    -- found no backups earlier than the given date)
    b.date IS NULL
    OR  
    -- Or there is a backup later than the row backup we are looking at.
    -- The LEFT JOIN condition joins each date to ALL the backups that
    -- happened on or before that date.  This condition excludes
    -- every backup for a given date except for the most recent one.
    -- If the backup is not the most recent backup on or before a 
    -- given date, there will exist a later backup that is also on
    -- or before that same date.
    NOT EXISTS ( SELECT 'later backup that is earlier than date'
                     FROM   backups b2
                     WHERE  b2.date <= d.date
                     AND    b2.date > b.date )
      )

Left Join most recent record, if one exists

One way to do this is to use a correlated subquery in the join that checks that there doesn't exists any event for the same person with a later date:

SELECT *
FROM tbl_people p
LEFT JOIN tbl_events e 
    ON p.People_UID = e.People_UID 
    AND NOT EXISTS (
       SELECT 1 FROM tbl_Events 
       WHERE e.People_UID = People_UID 
         AND Event_Date > e.Event_Date
    )
WHERE p.Active = 1

It might not be the most efficient solution though; maybe limiting the left joined set first is better. (Or come to think of it the exists should be better).

Given a sample data set like:

People_UID  Event_UID   People_UID  Event_Name          Event_Date
Jen         1           Jen         Had a Baby          2015-07-10
Jen         3           Jen         Bought a horse      2013-07-10
Shirley     NULL        NULL        NULL                NULL
Susan       NULL        NULL        NULL                NULL
Megan       2           Megan       Had a Baby          2014-08-05
Megan       4           Megan       Had another Baby    2015-08-05

This would be the result:

People_UID  Event_UID   People_UID  Event_Name          Event_Date
Jen         1           Jen         Had a Baby          2015-07-10
Shirley     NULL        NULL        NULL                NULL
Susan       NULL        NULL        NULL                NULL
Megan       4           Megan       Had another Baby    2015-08-05

Using joins it could look like this:

SELECT *
FROM tbl_people p
LEFT JOIN (
    SELECT e.* 
    FROM tbl_events e 
    JOIN (
       SELECT PEOPLE_UID, MAX(EVENT_DATE) MDATE 
       FROM tbl_Events GROUP BY People_UID
    ) A ON A.MDATE = E.event_date AND e.People_UID = A.People_UID 
) B ON p.People_UID = b.People_UID 
WHERE p.Active = 1

---

Related Topics