Mysql select last row for each group

Question

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 1

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
        )