I have a situation which can be boiled down to the following:
TABLE students
COLUMNS id, name, email
TABLE attendances
COLUMNS id, student_id, event_id
TABLE events
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
RESULT
Jane Doe | jane@uni.edu | 0
John Doe | john@uni.edu | 10
Will Doe | will@uni.edu | 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.