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!
CodePudding user response：
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;