How to Retrieve the Most Recent Attendance for Each Member in MySQL?

Select Only the Latest Value Using Group By in MySQL

When attempting to retrieve the most recent attendance for each member from a table, you may encounter a situation where you retrieve the initial attendance instead of the last one using a simple GROUP BY query. To resolve this issue, there are several approaches:

Using a Subquery

You can use a subquery to find the maximum timestamp for each member and then join the original table on that timestamp to retrieve the corresponding attendance:

<code class="sql">SELECT 
  id_branch_channel, id_member, attendance, timestamp, id_member
FROM view_event_attendance AS t1
WHERE id_event = 782
  AND timestamp = (
    SELECT 
      MAX(timestamp)
    FROM view_event_attendance AS t2 
    WHERE t1.id_member = t2.id_member 
      AND t1.id_event = t2.id_event 
    GROUP BY id_event, id_member
  )
OR timestamp IS NULL
GROUP BY id_event, id_member;</code>

Using a Derived Table

You can create a derived table that orders the rows by timestamp in descending order and then use that table as the source for your GROUP BY query:

<code class="sql">SELECT id_branch_channel, id_member, attendance, timestamp, id_member 
FROM (
  SELECT 
    id_branch_channel, id_member, attendance, timestamp, id_member
  FROM view_event_attendance 
  WHERE id_event = 782 
  ORDER BY timestamp DESC
) AS sorted
GROUP BY id_event, id_member;</code>

Using a Window Function

You can use a window function to find the maximum value across rows for each member and then use that maximum value to filter the rows:

<code class="sql">SELECT 
  id_branch_channel, id_member, attendance, timestamp, id_member
FROM (
  SELECT 
    id_branch_channel, id_member, attendance, timestamp, id_member,
    MAX(timestamp) OVER (PARTITION BY id_event, id_member) AS max_timestamp
  FROM view_event_attendance 
  WHERE id_event = 782
) AS subquery
WHERE timestamp = max_timestamp
GROUP BY id_event, id_member;</code>

Using a String Concatenation Trick

This approach involves concatenating the timestamp and attendance into a single string and using the MAX() function to find the largest string. By extracting the attendance from the maximum string, you effectively retrieve the latest attendance:

<code class="sql">SELECT 
  id_branch_channel, id_member, 
  SUBSTR(MAX(CONCAT(FROM_UNIXTIME(timestamp), attendance)), 20) AS attendance, timestamp, id_member
FROM view_event_attendance 
WHERE id_event = 782 
GROUP BY id_event, id_member;</code>

The best approach for your specific situation depends on the performance characteristics of your database and the size of your dataset.

The above is the detailed content of How to Retrieve the Most Recent Attendance for Each Member in MySQL?. For more information, please follow other related articles on the PHP Chinese website!