SQL max(), group by 和 order by
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6298703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
max(), group by and order by
提问by stilz
I have following SQL statement.
我有以下 SQL 语句。
SELECT t.client_id,max(t.points) AS "max" FROM sessions GROUP BY t.client_id;
It simply lists client id's with maximum amount of points they've achieved. Now I want to sort the results by max(t.points). Normally I would use ORDER BY, but I have no idea how to use it with groups. I know using value from SELECT list is prohibited in following clauses, so adding ORDER BY max at the end of query won't work.
它只是列出客户 ID 以及他们获得的最大积分。现在我想按 max(t.points) 对结果进行排序。通常我会使用 ORDER BY，但我不知道如何将它与组一起使用。我知道在以下子句中禁止使用 SELECT 列表中的值，因此在查询末尾添加 ORDER BY max 将不起作用。
How can I sort those results after grouping, then?
那么如何在分组后对这些结果进行排序？
Best regards
此致
回答by RedFilter
SELECT t.client_id, max(t.points) AS "max"
FROM sessions t
GROUP BY t.client_id
order by max(t.points) desc
回答by Peter Eisentraut
It is not quite correct that values from the SELECTlist are prohibited in following clauses. In fact, ORDER BYis logically processed after the SELECTlist and can refer to SELECTlist result names (in contrast with GROUP BY). So the normal way to write your query would be
SELECT在以下子句中禁止列表中的值是不完全正确的。实际上，ORDER BY是在SELECT列表之后进行逻辑处理，可以引用SELECT列表结果名称（与 相对GROUP BY）。所以编写查询的正常方法是
SELECT t.client_id, max(t.points) AS "max"
FROM sessions
GROUP BY t.client_id
ORDER BY max;
This way of expressing it is SQL-92 and should be very portable. The other way to do it is by column number, e.g.,
这种表达方式是 SQL-92，应该是非常可移植的。另一种方法是通过列号，例如，
ORDER BY 2;
These are the only two ways to do this in SQL-92.
这是在 SQL-92 中执行此操作的仅有的两种方法。
SQL:1999 and later also allow referring to arbitrary expressions in the sort list, so you could just do ORDER BY max(t.points), but that's clearly more cumbersome, and possibly less portable. The ordering by column number was removed in SQL:1999, so it's technically no longer standard, but probably still widely supported.
SQL:1999 及更高版本还允许引用排序列表中的任意表达式，因此您可以只执行ORDER BY max(t.points)，但这显然更麻烦，并且可能更不便于移植。SQL:1999 中删除了按列号排序，因此它在技术上不再是标准的，但可能仍然得到广泛支持。
回答by Andrew Lazarus
Since you have tagged as Postgres: Postgres allows a non-standardGROUP BYand ORDER BYcolumn number. So you could have
既然你已经标记为Postgres的：Postgres允许一个非标准GROUP BY和ORDER BY列数。所以你可以有
SELECT t.client_id, max(t.points) AS "max"
FROM sessions t
GROUP BY 1
order by 2 desc
After parsing, this is identical to RedFilter’s solution.
解析后，这与 RedFilter 的解决方案相同。
