I have a situation which can be boiled down to the following:
COLUMNS id, name, email
COLUMNS id, student_id, event_id
COLUMNS id, name, date_of_event
I want to be able to see all the students, and how many events they’ve attended, so I have:
select name, email, count(attendances.id) as number_of_shows_attended from students left join attendances on student_id = students.id group by name, email
Jane Doe | email@example.com | 0
John Doe | firstname.lastname@example.org | 10
Will Doe | email@example.com | 2
Perfect. Except I also wish to have a final column which lists the most recent event attended (which can be based on the
events.date_of_event date column).
I have tried simply adding the missing column, like so:
select name, email, count(attendances.id) as number_of_shows_attended, event.name as most_recent_event_attended from students left join attendances on student_id = students.id inner join events on attendances.event_id on events.id group by name, email
It gives me an event name as expected, but how can I ensure it is the most recent one? I tried adding
order by events.date_of_event desc, but of course it’s sorting the results of the query.