<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.infrastructure.core.version.dao.VersionDAO">
<select id="batchQueryTopVersionList"
parameterType="com.xxx.infrastructure.api.version.param.VersionQueryParam"
resultMap="com.xxx.infrastructure.core.version.dao.base.BaseVersionDAO.ResultMapWithBLOBs">
select a.* from
(select * from version WHERE is_deleted = 'n'
and biz_type = #{versionQueryParam.bizType}
and domain = #{versionQueryParam.domain}
<if test="versionQueryParam.bizIdList != null and versionQueryParam.bizIdList.size > 0">
and biz_id in
<foreach collection="versionQueryParam.bizIdList" separator="," index="index" open="(" close=")"
item="item">
#{item}
</foreach>
</if>
) a
join
(SELECT
biz_id,
history_id,
max(gmt_create) as gmt_create
FROM version
WHERE is_deleted = 'n'
and biz_type = #{versionQueryParam.bizType}
and domain = #{versionQueryParam.domain}
<if test="versionQueryParam.bizIdList != null and versionQueryParam.bizIdList.size > 0">
and biz_id in
<foreach collection="versionQueryParam.bizIdList" separator="," index="index" open="(" close=")"
item="item">
#{item}
</foreach>
</if>
group by biz_id) b
on concat(a.biz_id,a.gmt_create) = concat(b.biz_id,b.gmt_create)
</select>
</mapper>I have a mysql statement
代码语言：javascript复制SELECT *
FROM tbl_messages
WHERE to_user_id = '$user_id' OR from_user_id = '$user_id'
GROUP BY from_user_id
ORDER BY date_sent DESCand it is producing the correct results however they are not in the correct order.
The grouping works well but it record displayed in the group is the first recorded entered into the DB but I would like the latest record to be displayed in each group.
Is there a way to have the latest record displayed for each group?
代码语言：javascript复制2011-12-19 12:16:25 This is the first message
2011-12-19 12:18:20 This is the second message
2011-12-19 12:43:04 This is the third messageThe group shows 'This is the first message' where I would like 'This is the third message' as that is the most recent record/message.
Cheers.
This may work (but not guaranteed):
代码语言：javascript复制SELECT *
FROM
( SELECT *
FROM tbl_messages
WHERE to_user_id = '$user_id' OR from_user_id = '$user_id'
ORDER BY date_sent DESC
) tmp
GROUP BY from_user_id
ORDER BY date_sent DESCThis should work:
代码语言：javascript复制SELECT t.*
FROM
tbl_messages AS t
JOIN
( SELECT from_user_id
, MAX(date_sent) AS max_date_sent
FROM tbl_messages
WHERE to_user_id = '$user_id' OR from_user_id = '$user_id'
GROUP BY from_user_id
) AS tg
ON (tg.from_user_id, tg.max_date_sent) = (t.from_user_id, t.date_sent)
ORDER BY t.date_sent DESC参考资料
https://stackoverflow.com/questions/8556283/mysql-select-group-by-order
Kotlin开发者社区
专注分享 Java、 Kotlin、Spring/Spring Boot、MySQL、redis、neo4j、NoSQL、Android、JavaScript、React、Node、函数式编程、编程思想、"高可用，高性能，高实时"大型分布式系统架构设计主题。
High availability, high performance, high real-time large-scale distributed system architecture design。
分布式框架：Zookeeper、分布式中间件框架等 分布式存储：GridFS、FastDFS、TFS、MemCache、redis等 分布式数据库：Cobar、tddl、Amoeba、Mycat 云计算、大数据、AI算法 虚拟化、云原生技术 分布式计算框架：MapReduce、Hadoop、Storm、Flink等 分布式通信机制：Dubbo、RPC调用、共享远程数据、消息队列等 消息队列MQ：Kafka、MetaQ，RocketMQ 怎样打造高可用系统：基于硬件、软件中间件、系统架构等一些典型方案的实现：HAProxy、基于Corosync Pacemaker的高可用集群套件中间件系统 Mycat架构分布式演进 大数据Join背后的难题：数据、网络、内存和计算能力的矛盾和调和 Java分布式系统中的高性能难题：AIO，NIO，Netty还是自己开发框架？ 高性能事件派发机制：线程池模型、Disruptor模型等等。。。
合抱之木，生于毫末；九层之台，起于垒土；千里之行，始于足下。不积跬步，无以至千里；不积小流，无以成江河。
