How to Retrieve the Last Value for a Group in MySQL Using `GROUP BY`?

In MySQL, selecting the last value for a given group of rows using GROUP BY can be achieved, but not directly. The technique used to accomplish this is to first concatenate the desired column with a unique identifier, such as a timestamp, before performing the grouping.

Consider the following query:

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

In this query, we concatenate the timestamp and attendance columns for each row into a single string. This allows us to capture the last recorded attendance associated with each unique pair of id_branch_channel and id_member. The MAX() function is then used to select the maximum value of the concatenated string, effectively giving us the last recorded attendance for each group.

Once the last attendance has been identified, we can use the SUBSTRING() function to extract only the attendance value from the concatenated string. This can be achieved with the following query:

<code class="sql">SELECT id_branch_channel, id_member, SUBSTRING(last_attendance, 19) AS attendance
FROM (
    SELECT id_branch_channel, id_member, MAX(CONCAT(timestamp, attendance)) AS last_attendance
    FROM view_event_attendance
    WHERE id_event = 782
    GROUP BY id_branch_channel, id_member
) AS subquery;</code>

This query uses a subquery to perform the initial grouping and concatenation, and then selects only the attendance column from the result using the SUBSTRING() function.

By using this technique, we can effectively select the last recorded attendance for each group of rows in the view_event_attendance table.

The above is the detailed content of How to Retrieve the Last Value for a Group in MySQL Using `GROUP BY`?. For more information, please follow other related articles on the PHP Chinese website!