Mysql - 如何使用 GROUP BY 获取最后一条记录

Question

我有如下表格:

id   |   deptid   |   last_activities
1    |     1      |   MMD PURCASHING
2    |     1      |   JKO / REPORTING
3    |     2      |   STAND BY CC
4    |     3      |   JKO / REPORTING

我的查询:

SELECT id, deptid, last_activities FROM dept_activities GROUP BY deptid ORDER BY id DESC

结果如下:

id   |   deptid   |   last_activities
4    |     3      |   JKO / REPORTING
3    |     2      |   STAND BY CC
1    |     1      |   MMD PURCASHING

我想要这样的结果:

id   |   deptid   |   last_activities
4    |     3      |   JKO / REPORTING
3    |     2      |   STAND BY CC
2    |     1      |   JKO / REPORTING

怎么可能呢？怎么正确查询？

Answer 1

请试试这个:

SELECT * FROM dept_activities AS da1
JOIN
(SELECT MAX(id) as max_id FROM dept_activities GROUP BY deptid DESC) AS da2
ON (da1.id = da2.max_id);