当使用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