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

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:

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

try this –

  FROM Data1
  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

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.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.