user2178521 user2178521 - 4 years ago 317
SQL Question

Mysql select last row for each group

SELECT
MAX('time'), c_id, message, state, time
FROM message
WHERE receive = 1
GROUP BY c_id


I have a mysql query, I try to select the last row of each group, but it's not working.

c_id is the group. It select the first row of each group now.

Answer Source

First, you should not escape the column name with single quote since it is not string literal.
Second, you can do subquery which separately get the latest time for every c_id and join it back with the original table to get the other columns.

SELECT  a.*
FROM    message a
        INNER JOIN
        (
            SELECT  c_id, MAX(time) time
            FROM    message
            GROUP   BY c_id
        ) b ON a.c_id = b.c_id AND
                a.time = b.time

or

SELECT  a.*
FROM    message a
WHERE   a.time =
        (
            SELECT  MAX(time) time
            FROM    message b
            WHERE   a.c_id = b.c_id
        ) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download