对于group by 和 order by 并用 的分析

 

今天朋友问我一个sql查询。

 

需求是 找到idapi最近那条数据,说明idapi 是重复的，于是就简单的写了

　　SELECT * FROM `ag_alarm_history`   group by `idApi` order by `createTime` desc

发现结果不对

于是 网上查阅资料得到

group by order by 连用 无效 可以使用一下的替换

SELECT
    `idAlarmHistory`,
    SUBSTRING_INDEX(
        group_concat(
            `idApi`
            ORDER BY
                `createTime` DESC
        ),
        ',',
        1
    ) AS priCommodityID,
        SUBSTRING_INDEX(
        group_concat(
            `createTime`
            ORDER BY
                `createTime` DESC
        ),
        ',',
        1
    ) AS createDate
FROM
    ag_alarm_history
WHERE
    1
GROUP BY
    `idApi`;

转载一篇详细的文章

https://www.cnblogs.com/devzxd/p/mysql-groupby-orderby.html