Mysql – select members with the longest running absence streak

MySQLsyntax

I have a database containing a table that stores attendance data, and its columns are member_id and datetime_attended.

And another table storing members info, which is made up by the columns id and name.

Each time a member attends an event, a new row containing the member.id and the current timestamp is inserted into to the attendance table.

I would like to formulate a mysql query to pull an ordered list of the top 7 members who have the longest running absence streaks (the streaks must be currently running streaks — past absence streaks are ignored), as well as their most recent attendance dates.

After much searching, reading and many query attempts, I've yet to stumble on a working query that's even worth sharing here, as all my attempts fell embarrassingly short of the goal (and they would probably just be confusing and frustrating to read). But the general idea I've been working at is to JOIN the two tables ON member.id with GROUP BY and ORDER BY parameters to sort and extract the single most recent attendance entry per member, then use another ORDER BY and LIMIT to get the 7 longest running streaks from the result.

This is my first question post, so I apologize if this question is lacking in any way. Thank you.

Best Answer

This is your "general idea" translated to SQL

select m.id, m.name, max(a.datetime_attended) as max_attened
from attendance as a
join members as m
  on m.id = a.member_id 
group by m.id, m.name
order by max_attened
limit 7
Related Question