How to Join the Most Recent Row in One Table to Another Table

How do I join the most recent row in one table to another table?

I do it this way:

SELECT e.*, s1.score, s1.date_added 
FROM entities e
  INNER JOIN scores s1
    ON (e.id = s1.entity_id)
  LEFT OUTER JOIN scores s2
    ON (e.id = s2.entity_id AND s1.id < s2.id)
WHERE s2.id IS NULL;

Update a table by joining to the most recent record of another table that meets some condition

You can use a CTE to do this, as so:

;with maxParts as
(
    select Area, SequenceNumber, MAX(dateadded) as maxDateAdded 
    from Part
    group by Area, SequenceNumber
)
UPDATE f
SET f.StartTime = p.maxDateAdded
from @Filters f
    inner join maxParts p 
        on f.Area = p.Area
        and f.StartingSeqNum = p.SequenceNumber

However, you can also rewrite the CTE as a subquery:

UPDATE f
SET f.StartTime = p.maxDateAdded
from @Filters f
    inner join (
            select Area, SequenceNumber, MAX(dateadded) as maxDateAdded 
            from Part
            group by Area, SequenceNumber
        ) p 
        on f.Area = p.Area
        and f.StartingSeqNum = p.SequenceNumber

The query plans for these should be identical - all the CTE really allows you is cleaner-looking queries and the ability to easily reuse that SELECT.

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 )
      )

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.

MySQL JOIN the most recent row only?

You may want to try the following:

SELECT    CONCAT(title, ' ', forename, ' ', surname) AS name
FROM      customer c
JOIN      (
              SELECT    MAX(id) max_id, customer_id 
              FROM      customer_data 
              GROUP BY  customer_id
          ) c_max ON (c_max.customer_id = c.customer_id)
JOIN      customer_data cd ON (cd.id = c_max.max_id)
WHERE     CONCAT(title, ' ', forename, ' ', surname) LIKE '%Smith%' 
LIMIT     10, 20;

Note that a JOIN is just a synonym for INNER JOIN.

Test case:

CREATE TABLE customer (customer_id int);
CREATE TABLE customer_data (
   id int, 
   customer_id int, 
   title varchar(10),
   forename varchar(10),
   surname varchar(10)
);

INSERT INTO customer VALUES (1);
INSERT INTO customer VALUES (2);
INSERT INTO customer VALUES (3);

INSERT INTO customer_data VALUES (1, 1, 'Mr', 'Bobby', 'Smith');
INSERT INTO customer_data VALUES (2, 1, 'Mr', 'Bob', 'Smith');
INSERT INTO customer_data VALUES (3, 2, 'Mr', 'Jane', 'Green');
INSERT INTO customer_data VALUES (4, 2, 'Miss', 'Jane', 'Green');
INSERT INTO customer_data VALUES (5, 3, 'Dr', 'Jack', 'Black');

Result (query without the LIMIT and WHERE):

SELECT    CONCAT(title, ' ', forename, ' ', surname) AS name
FROM      customer c
JOIN      (
              SELECT    MAX(id) max_id, customer_id 
              FROM      customer_data 
              GROUP BY  customer_id
          ) c_max ON (c_max.customer_id = c.customer_id)
JOIN      customer_data cd ON (cd.id = c_max.max_id);

+-----------------+
| name            |
+-----------------+
| Mr Bob Smith    |
| Miss Jane Green |
| Dr Jack Black   |
+-----------------+
3 rows in set (0.00 sec)

SQL join each row in a table with a one row from another table

When there is guaranteed to be a single oldest window (i.e. no two Start times are the same for any ISBN)

with activity_window as (
  select
    a.`Timestamp`,
    a.`ISBN`,
    w.`Start`,
    w.`End`,
    row_number() over (partition by a.`ISBN`, a.`Timestamp` order by w.`Start`) rn
  from
    `Activity` a
    inner join `Window` w on a.`ISBN` = w.`ISBN` and a.`Timestamp` between w.`Start` and w.`End`
)
select `Start`, `End`, `ISBN`, `Timestamp` from activity_window where rn = 1;

Result:

WITH n_node as (
    SELECT "Name", "Attribute",
           row_number() over (partition by "Name" order by "Date" DESC) rn
    FROM Nodes 
), 
n_vector as (
    SELECT "Node", "V_NAME", "color",
           row_number() over (partition by "Node", "V_NAME" order by "Date" DESC) rn
    FROM Vectors 
)
SELECT "Name", "Attribute", "V_NAME", "color"
FROM n_node
JOIN n_vector 
  ON n_node.rn = n_vector.rn
 AND n_node.rn = 1
 AND n_node."Name" = n_vector."Node"
ORDER BY "Name" DESC

| Name | Attribute | V_NAME | color |
|------|-----------|--------|-------|
|   14 |        A2 |     V1 |   red |
|   14 |        A2 |     V2 |  blue |
|   12 |        B1 |     V3 | black |
|   12 |        B1 |     V4 | black |

SELECT contract_lines.*,T2.units
FROM contract_lines
LEFT JOIN (
    SELECT contract_id, line_id, MAX(date_changed) AS maxdate
    FROM contract_history
    GROUP BY contract_id, line_id) AS T1 
    JOIN contract_history T2 ON 
         T1.contract_id=T2.contract_id and 
         T1.line_id= T2.line_id and 
         T1.maxdate=T2.date_changed
ON contract_lines.contract_id = T1.contract_id
AND contract_lines.line_id = T1.line_id

SELECT c.*,
       x.*
  FROM CASES c
  JOIN NOTES x ON x.case_id = c.case_id
  JOIN (SELECT n.case_id,
               MAX(n.note_date) AS max_note_date
          FROM NOTES n
      GROUP BY n.case_id) y ON y.case_id = x.case_id
                           AND y.max_note_date = x.note_date

   SELECT c.*,
          x.*
     FROM CASES c
LEFT JOIN NOTES x ON x.case_id = c.case_id
     JOIN (SELECT n.case_id,
                  MAX(n.note_date) AS max_note_date
             FROM NOTES n
         GROUP BY n.case_id) y ON y.case_id = x.case_id
                              AND y.max_note_date = x.note_date