mysql - 根据job ID显示最新的ID和公司ID

Question

表:服务

id companyid jobid
 1         1     1
 2         1     2
 3         1     2
 4         2     3
 5         3     4
 6         3     1
 7         4     2
 8         5     2

我键入以下查询:

SELECT *
FROM service
WHERE jobID = 2
ORDER BY companyID desc, ID desc

我得到以下输出:

id companyid jobid
 8         5     2
 7         4     2
 3         1     2 
 2         1     2

但我希望我的预期输出如下:

id companyid jobid
 8         5     2
 7         4     2
 3         1     2 

如何修改查询以获得预期的输出？

Answer 1

您应该能够将 max 函数与 group by 子句一起应用:

select max(id) id, companyid, jobid
from service
where jobid = 2
group by companyid, jobid
order by id desc

根据您的示例数据，结果将是:

id  companyid   jobid
8   5           2
7   4           2
3   1           2