How to Retrieve the Last Attendance Record for Each Member in MySQL?

Selecting the Last Value for Each Group Using MySQL

Introduction

In your MySQL table, you have data about event attendance, and you want to retrieve the last attendance recorded for each member. Despite using a GROUP BY clause, you're encountering an issue where the first attendance is being selected instead of the last one. Let's explore how to address this problem.

Using a Subquery and ORDER BY

One approach is to use a subquery and ORDER BY to select the last attendance for each member:

SELECT id_branch_channel, id_member, attendance, timestamp, id_member
FROM (
    SELECT *
    FROM view_event_attendance
    ORDER BY timestamp DESC
) AS t1
WHERE id_event = 782
GROUP BY id_event, id_member;

This query retrieves all rows from the view_event_attendance table, orders them by timestamp in descending order (i.e., most recent first), and then uses a subquery as the source for the main query. The GROUP BY clause is used to group the results by member and select the last attendance.

JOINing with a Subquery

Another option is to use a JOIN with a subquery that finds the maximum timestamp for each member:

SELECT 
    t1.id_branch_channel,
    t1.id_member, 
    t1.attendance, 
    t1.timestamp,
    t1.id_member
FROM view_event_attendance AS t1
JOIN (
    SELECT id_member, MAX(timestamp) AS max_timestamp
    FROM view_event_attendance
    GROUP BY id_member
) AS t2 ON t1.id_member = t2.id_member AND t1.timestamp = t2.max_timestamp
WHERE t1.id_event = 782;

In this query, the view_event_attendance table is aliased as t1 and is joined with a subquery (aliased as t2) that calculates the maximum timestamp for each member. The JOIN condition matches rows based on the member ID and the maximum timestamp, ensuring that only the last attendance for each member is included in the results.

Using Custom Aggregation and Substring

A more efficient solution is to use a custom aggregation and substring instead of subqueries:

SELECT id_branch_channel, id_member, 
    substring(max(concat(from_unixtime(timestamp), attendance)) from 20) as attendance
FROM view_event_attendance
WHERE id_event = 782
GROUP BY id_event, id_member;

This query combines the timestamp and attendance into a single string for each row and uses max() to select the maximum value within each member group. The substring() function then extracts the attendance portion of the concatenated string. This approach avoids the performance overhead associated with subqueries.

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