How to conditionally use a JOIN in mysql to pull the most recent matching record in the joined table

[orclord1]

I’m an inexperienced coder and I am unable to figure out how to have my LEFT JOIN add the matching data based on only the most recent timestamped value in the joined table.

In Data2 table, I have a “set_id” value that might exists multiple times, however with different timestamps. Data2 captures the columns: ‘set_id’, ‘set_name’, ‘dateChange’ whenever ‘set_name’ has changed, at which time ‘dateChange’ is updated with the current TIMESTAMP.

I am creating a query where I am left joining Data2 to Data1 based on the ‘set_id’. However as part of this join, I only want to join the newest ‘dateChange’ value for ‘set_id’ in Data2.

Based on my Google searches, there seems to be multiple ways to approach this and I think one involves doing a subquery. I’m not experienced with using sub queries or the max function, so could someone please confirm if this syntax would be correct:

SELECT * 
FROM Data1
  LEFT JOIN (
    SELECT sets_id, sets_name, max(changeDate) FROM Data2
  ) 
  AS mySubQuery 
  ON Data1.sets_id = mySubQuery.sets_id
WHERE sets_id = 25

[r937]

try this –

SELECT * 
  FROM Data1
LEFT 
  JOIN ( SELECT sets_id
              , sets_name
              , RANK() 
                  OVER(PARTITION BY sets_id
                           ORDER BY changeDate DESC) AS rnk
           FROM Data2 ) AS d2
    ON d2.sets_id = Data1.sets_id
   AND d2.rnk = 1

[orclord1]

Thank you. Seconds before you replied, I managed to figure out a way to get the combo of the max() and the subquery working.

I will also experiment with your method as well, as it should help me learn some functionality of mysql syntax.