CODEWITHSUNDEEP
mysqljoinmax
nomysz
nomysz

Reputation: 237

How to mysql join by two columns and get only newest records from joined table?

I have tables setting, location and result.

I would like to get newest result (max id or date column) for each pair setting + location.

So if I have 4 settings with 2 locations each, I would like to get 8 rows with newest result id for each setting + result.

This query works partially. I got pairs setting + location but then result joined is the newest result for setting (I need for setting and location individually).

SELECT s.id, r.id AS last_result_id, r.date AS last_result_date
FROM result r
JOIN (SELECT max(r.id) max_id, r.setting_id
     FROM result r
     GROUP BY r.setting_id) r_sub 
     ON r.id = r_sub.max_id
JOIN setting s ON r.setting_id = m.id;

Any suggestions?

Upvotes: 0

Views: 21

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30819

You can use a simple JOIN between Setting and Location tables and get maximum result id for each, e.g.:

SELECT s.id, l.id, 
(SELECT MAX(r.id) FROM result WHERE r.setting_id = s.id) As result_id
FROM settings s JOIN location l ON s.id = l.setting_id;

Upvotes: 2

Related Questions