Join to Only the "Latest" Record with T-Sql

SQL join: selecting the last records in a one-to-many relationship

This is an example of the greatest-n-per-group problem that has appeared regularly on StackOverflow.

Here's how I usually recommend solving it:

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
    (p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id)))
WHERE p2.id IS NULL;

Explanation: given a row p1, there should be no row p2 with the same customer and a later date (or in the case of ties, a later id). When we find that to be true, then p1 is the most recent purchase for that customer.

Regarding indexes, I'd create a compound index in purchase over the columns (customer_id, date, id). That may allow the outer join to be done using a covering index. Be sure to test on your platform, because optimization is implementation-dependent. Use the features of your RDBMS to analyze the optimization plan. E.g. EXPLAIN on MySQL.

---

Some people use subqueries instead of the solution I show above, but I find my solution makes it easier to resolve ties.

Join to only the latest record with t-sql

select a.*, bm.MaxRowDate
from (
    select TableAID, max(RowDate) as MaxRowDate
    from TableB
    group by TableAID
) bm
inner join TableA a on bm.TableAID = a.ID

If you need more columns from TableB, do this:

select a.*, b.* --use explicit columns rather than * here
from (
    select TableAID, max(RowDate) as MaxRowDate
    from TableB
    group by TableAID
) bm
inner join TableB b on bm.TableAID = b.TableAID
    and bm.MaxRowDate = b.RowDate
inner join TableA a on bm.TableAID = a.ID

SQL join query, want to get the latest record of joining table (or null if it doesn't match)

You can use left join and group by:

select u.id, u.name, max(l.date)
from user u left join
     login l
     on l.id = u.id
group by u.id, u.name;

Note: This includes the id as well. If name is known to be unique that is not necessary.

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.

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;

How To: How to extract the latest record with a SQL SELECT JOIN

You can try to do like this :

SELECT a.*, p1.*
FROM ENT_Instance a
JOIN VW_Instance p1 ON (a.id = p1.ENTI)
LEFT OUTER JOIN ENT_Instance p2 ON (a.id = p2.ENTI AND 
    (p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id)))
WHERE {condition};

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

---

Related Topics