MySQL为每个组选择最后一行 [英] Mysql select last row for each group

问题描述

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

我有一个mysql查询，我尝试选择每个组的最后一行，但是它不起作用.

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

c_id是组.现在，它选择每个组的第一行.

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

推荐答案

首先，您不应使用单引号将列名转义，因为它不是字符串文字.
其次，您可以进行子查询，该子查询分别获取每个c_id的最新时间，并将其与原始表重新连接以获取其他列.

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

或

SELECT  a.*
FROM    message a
WHERE   a.time =
        (
            SELECT  MAX(time) time
            FROM    message b
            WHERE   a.c_id = b.c_id
        ) 

这篇关于MySQL为每个组选择最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！