Select last registered row from each group from two tables in MySQL v5.6

Question

Could you please help me with a query I'm having issues with? I tried to seach for similar questions here with no success.

I have 2 tables which I need to join and get the latest created_date from each val1, val2 unique combination.

See here for the 2 tables details: https://www.db-fiddle.com/f/87hqeMqP7sf68fxbsywm5G/0

The expected result would be this:

val1	val2	num1	num2	created_date
X	A	33	333	2022-11-03
X	B	66	666	2022-11-06
X	C	88	888	2022-11-08
X	D	99	999	2022-11-09
Y	A	111	1111	2022-11-11

Please use MySQL v.5.6. Thanks in advance!

Answer 1

A subquery would help in this case

SELECT d.val1, 
       d.val2, 
       s.num1, 
       s.num2, 
       mx_dt.max_dt
FROM scan AS s 
INNER JOIN dir AS d on s.t2id=d.t2id
INNER JOIN ( SELECT t2id, 
                    max(created_date) as max_dt
             FROM scan 
             GROUP BY t2id
          )   as mx_dt on mx_dt.t2id = s.t2id and mx_dt.max_dt=s.created_date;

https://dbfiddle.uk/Owdaf5Lx