How to Select Only the Last Value in a Grouped Table Using MySQL?

Selecting Only the Last Value Using GROUP BY in MySQL

Problem:

In a table containing multiple rows of attendance data for users, the goal is to obtain only the last attendance value for each user, grouped by user ID. However, using GROUP BY with order clauses does not return the desired result.

Solution:

To select only the last attendance value, use the following approach:

1. Create a subquery to select the ID, timestamp, and attendance for each row. Order the rows in descending order of timestamp to retrieve the latest attendance for each user.

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

2. Join the subquery back to the original table using IN to select the rows where the timestamp matches the last_timestamp from the subquery.

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

3. Alternatively, use a correlated subquery to select the last attendance for each user.

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

Note: This solution avoids using GROUP BY with ORDER BY clauses, which can be inefficient for large tables.

The above is the detailed content of How to Select Only the Last Value in a Grouped Table Using MySQL?. For more information, please follow other related articles on the PHP Chinese website!