Mysql – Selecting most recent date when dates are stored in another table

greatest-n-per-groupgroup byjoin;MySQLselect

Working in a MySQL database, I have tables set up like this:

Table song_instances has song_id (int), meeting_instance_id (int)

The meeting_instances table has columns meeting_instance_id (int), meeting_date (datetime), meeting_id (int).

Each meeting_id (referencing the meetings table) might have numerous meeting_instances, and each meeting_instance might have numerous songs associated with it.

I'm trying to get a list of all songs from song_instances that are related to a particular meeting type (meeting_id)and include the date of the most recent date that the song has been used. Because the meeting_id and the meeting_date are only referenced in the meeting_instances table, I am joining that table in. But every attempt falls short.

Example:
song_instances:

song_id     meeting_instance_id
1           1
1           2
2           2
2           3
3           1
1           5
3           5

Meeting Instances:

meeting_instance_id      meeting_id       meeting_date
1                        4                2014-09-01
2                        4                2014-09-02
3                        2                2014-09-03
5                        4                2014-09-04

I want to get back:

song_id         lastDate
1               2014-09-04
2               2014-09-03
3               2014-09-04

etc.

Here is my most recent (and closest to successful) attempt.

SELECT si.song_id, mi.lastDate FROM song_instances si LEFT JOIN 
        (SELECT MAX(meeting_date) as lastDate, meeting_id, meeting_instance_id FROM meeting_instances WHERE meeting_id = 4 GROUP BY meeting_instance_id) as mi 
    ON si.meeting_instance_id = mi.meeting_instance_id                                  
    GROUP BY si.song_id ORDER BY lastDate DESC

This currently gives me the date farthest in the past rather than the most recent date.
Not sure why. Any thoughts on how to improve this query?

This is a derived table from a much larger query, that is selecting song names and topics from other tables, but this is the only part of the whole query that is not working as intended.

Best Answer

Please try the following.

If you need most recent date for every song over all meeting instances:

select song_id, max(meeting_date)
from song_instances si
    join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id
group by song_id

If you're interested only in particular meeting (meeting_id = 4):

select song_id, max(meeting_date)
from song_instances si
    join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id
where meeting_id = 4
group by song_id
Related Question