I have a database containing a table that stores
attendance data, and its columns are
And another table storing
members info, which is made up by the columns
Each time a
member attends an
event, a new row containing the
member.id and the current
timestamp is inserted into to the
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
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.