1

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.

3
  • How do you define absence if only presence is recorded? Time since the latest datetime_attended?
    – dnoeth
    Jun 4, 2015 at 15:23
  • thank you both for the constructive feedback. to answer the first question, it is indeed a many-to-many (members to events), but the extra table may not be necessary at this time, as there is actually only one event, but it is recurring at the first of every month. as for the semantics of "absence," yes, it is as you guessed: duration since the latest datetime_attended.
    – dan
    Jun 4, 2015 at 15:28
  • 1
    actually, let me correct myself.. I'm not in a position to know whether or not the extra table is needed/beneficial in this scenario
    – dan
    Jun 4, 2015 at 15:29

1 Answer 1

1

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
1
  • yes! that's exactly the result I was looking for. thank you
    – dan
    Jun 4, 2015 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.