mysql 获取分组中最新记录SQL写法示例 select group by order by

<?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

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 DESC

and 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?

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 message

The 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):

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 DESC

This should work:

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模型等等。。。

合抱之木，生于毫末；九层之台，起于垒土；千里之行，始于足下。不积跬步，无以至千里；不积小流，无以成江河。

最后编辑于：2020.05.22 00:48:43