在没有子查询的情况下，在MySQL中通过GROUP BY优先考虑ORDER BY [英] Give priority to ORDER BY over a GROUP BY in MySQL without subquery

问题描述

我有下面的查询做我想做的，但我怀疑可以做到这一点，没有子查询：

  SELECT * 
 FROM（SELECT * 
 FROM'versions'
 ORDER BY'ID'DESC）AS X 
 GROUP BY'program'
  

我需要的是按程序进行分组，但返回具有最高值ID的版本中的对象的结果。

在过去的经验中，像这样的查询应该在MySQL中工作，但由于某种原因，它不是：

pre $ SELECT
FROM'versions'
GROUP BY'program'
ORDER BY MAX（'ID'）DESC

我希望做的事情是让MySQL首先执行ORDER BY，然后然后 GROUP BY，但它坚持先执行GR​​OUP BY，然后再执行ORDER BY。即它正在对分组的结果进行排序，而不是对排序的结果进行分组。

当然，不可能写

SELECT * FROM'versions'ORDER BY'ID'DESC GROUP BY'program'

解决方案

只要（程序，id）上有复合索引， 。子查询应该只检查每个程序分支的第一个id，并快速从外部查询中检索所需的记录。

  select v。* 
 from 
（
选择程序，MAX（id）id 
从版本
按程序分组
）m 
内连接版本v上m.program = v.program和m.id = v.id 
  

I have the following query which does what I want, but I suspect it is possible to do this without a subquery:

  SELECT * 
    FROM (SELECT * 
            FROM 'versions' 
        ORDER BY 'ID' DESC) AS X 
GROUP BY 'program'

What I need is to group by program, but returning the results for the objects in versions with the highest value of "ID".

In my past experience, a query like this should work in MySQL, but for some reason, it's not:

  SELECT * 
    FROM 'versions' 
GROUP BY 'program' 
ORDER BY MAX('ID') DESC

What I want to do is have MySQL do the ORDER BY first and then the GROUP BY, but it insists on doing the GROUP BY first followed by the ORDER BY. i.e. it is sorting the results of the grouping instead of grouping the results of the ordering.

Of course it is not possible to write

SELECT * FROM 'versions' ORDER BY 'ID' DESC GROUP BY 'program'

Thanks.

解决方案

This should do it and work pretty well as long as there is a composite index on (program,id). The subquery should only inspect the very first id for each program branch, and quickly retrieve the required record from the outer query.

select v.*
from
(
    select program, MAX(id) id
    from versions
    group by program
) m
inner join versions v on m.program=v.program and m.id=v.id

这篇关于在没有子查询的情况下，在MySQL中通过GROUP BY优先考虑ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！