如何按DESC顺序分组 [英] How to group by DESC order

问题描述

我有以下表格问题：

  ID | asker 
 1 | Bob 
 2 | Bob 
 3 | Marley 
  

我想选择每个asker一次，如果有多个同名的askers，一个最高的id。所以，预期的结果：

  ID | asker 
 3 | Marley 
 2 | Bob 
  

我使用以下查询：

  SELECT * FROM questions GROUP by questions.asker ORDER by questions.id DESC 
  

$ b b

我得到以下结果：

  ID | asker 
 3 | Marley 
 1 | Bob 
  

因此，它会选择遇到的第一个Bob，而不是最后一个。

感谢

解决方案

如果你想要最后的 / code>每个 asker ，那么你应该使用一个聚合函数：

  SELECT max（id）as id，
 asker 
 FROM questions 
 GROUP by asker 
 ORDER按id DESC 
  

您得到不寻常结果的原因是因为MySQL使用 GROUP BY 的扩展允许选择列表中的项非聚合，并且不包括在GROUP BY子句中。这可能导致意想不到的结果，因为MySQL可以选择返回的值。 （请参见 MySQL扩展到GROUP BY ） / p>



从MySQL文档：

MySQL扩展了GROUP BY的使用，选择列表可以引用在GROUP BY子句中未命名的非聚集列。 ...您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能。但是，当GROUP BY中未命名的每个非聚集列中的所有值对于每个组都相同时，这是很有用的。服务器可以自由选择每个组中的任何值，因此除非它们相同，否则选择的值是不确定的。此外，通过添加ORDER BY子句不会影响从每个组中选择值。结果集的排序在选择值后发生，ORDER BY不影响服务器选择的值。




I have the following table called questions:

ID | asker 
1  | Bob
2  | Bob
3  | Marley

I want to select each asker only once and if there are multiple askers with the same name, select the one of the highest id. So, the expected results:

ID | asker 
3  | Marley
2  | Bob

I use the following query:

SELECT * FROM questions GROUP by questions.asker ORDER by questions.id DESC

I get the following result:

ID | asker 
3  | Marley
1  | Bob

So it selects the first 'Bob' it encounters instead of the last one.

Thanks

解决方案

If you want the last id for each asker, then you should use an aggregate function:

SELECT max(id) as id, 
   asker
FROM questions 
GROUP by asker 
ORDER by id DESC

The reason why you were getting the unusual result is because MySQL uses an extension to GROUP BY which allows items in a select list to be nonaggregated and not included in the GROUP BY clause. This however can lead to unexpected results because MySQL can choose the values that are returned. (See MySQL Extensions to GROUP BY)

From the MySQL Docs:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

这篇关于如何按DESC顺序分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！