当使用GROUP BY 分组，默认返回的数据是组中最小的记录即id最小的数据，

当开发中经常会需要分组后将最新的数据放在前面，

为了实现需求，使用了嵌套查询，分别使用order by来排序

SELECT
    id,
    from_id,
    to_id,
    conversation_id,
    created_date,
    has_read,
    content
FROM
    ( SELECT * FROM message WHERE from_id = 6 OR to_id = 6 ORDER BY created_date DESC) AS tt 
GROUP BY
    conversation_id 
ORDER BY
    created_date DESC

 

 但执行结果依然是id最小的那条数据 

 

 

最终添上limit max_size 后才生效

SELECT
    id,
    from_id,
    to_id,
    conversation_id,
    created_date,
    has_read,
    content
FROM
    ( SELECT * FROM message WHERE from_id = 6 OR to_id = 6 ORDER BY created_date DESC limit 100) AS tt 
GROUP BY
    conversation_id 
ORDER BY
    created_date DESC

 

版权声明：本文为mjbrian原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/mjbrian/p/13955070.html