带排序依据的 SQL 组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1708694/
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
SQL Group with Order by
提问by qui
This feels like it should have a basic solution but I don't seem to be getting it.
感觉它应该有一个基本的解决方案，但我似乎没有得到它。
Take this query:
拿这个查询：
SELECT Category FROM Article
GROUP BY Category
I want to effectively do this:
我想有效地做到这一点：
SELECT Category, DatePublished FROM Article
GROUP BY Category
ORDER BY DatePublished DESC
I don't really want to select DatePublished, but it seemed to make sense to order by it. That doesn't work though.
我真的不想选择 DatePublished，但按它订购似乎很有意义。但这行不通。
Basically I want to order categories by the most recent DatePublished article.
基本上我想按最新的 DatePublished 文章对类别进行排序。
回答by Quassnoi
SELECT Category
FROM Article
GROUP BY
Category
ORDER BY
MAX(DatePublished) DESC
Since you do a GROUP BY, you need to run some aggregate function over non-grouping columns.
由于您执行 a GROUP BY，您需要在非分组列上运行一些聚合函数。
MAXwill select the date of last published article from each category and order categories accordingly.
MAX将从每个类别中选择最后发表文章的日期并相应地对类别进行排序。
回答by Johannes Weiss
In aggregations (--> GROUP BY), you can only select/use fields in combination with aggregation functions (e.g. SUM, MAX, MIN) and fields listed in the GROUP BY-clause.
在聚合 (--> GROUP BY) 中，您只能选择/使用与聚合函数（例如SUM, MAX, MIN）和GROUP BY-clause 中列出的字段结合使用的字段。
Simple sample:
简单示例：
A | B
-----+-----
1 | 2
1 | 3
if you'd write SELECT A,B FROM table GROUP BY A, that would yield:
如果你写SELECT A,B FROM table GROUP BY A，那会产生：
A | B
-----+-----
1 |{2,3}
but that is not possible (Bhas 2 values in one row!?!). You have to do something with the values of Bwhich groups them together, too. So two possibilities:
但这是不可能的（B在一行中有 2 个值！？！）。您也必须对将B它们组合在一起的值做一些事情。所以有两种可能：
1: Add Bin the GROUP BY-clause
1：添加B在GROUP BY-clause
SELECT A,B FROM table GROUP BY A,B
yields
产量
A | B
-----+-----
1 | 2
1 | 3
2: Use an aggregation function on B
2: 使用聚合函数B
SELECT A,MAX(B) FROM TABLE GROUP BY A,B
gives you
给你
A | B
-----+-----
1 | 3
The same arguments apply to the ORDER BYclause.
相同的论据适用于该ORDER BY条款。
Most of the times when you want to write a statement like the first one I showed up with, possibility 1 is the solution, since you may know that Aand Bbelong together (common sample: UserIdand UserName) but the RDBMS does not know it!
大多数情况下，当您想编写像我出现的第一个语句一样的语句时，可能性 1 是解决方案，因为您可能知道A并且B属于一起（常见示例：UserId和UserName），但 RDBMS 不知道！
