SQL 如何在同一个 SELECT 语句中使用 DISTINCT 和 ORDER BY？

How to use DISTINCT and ORDER BY in same SELECT statement?

提问by r.r

After executing the following statement:

执行以下语句后：

SELECT  Category  FROM MonitoringJob ORDER BY CreationDate DESC

I am getting the following values from the database:

我从数据库中获取以下值：

test3
test3
bildung
test4
test3
test2
test1

but I want the duplicates removed, like this:

但我希望删除重复项，如下所示：

bildung
test4
test3
test2
test1

I tried to use DISTINCT but it doesn't work with ORDER BY in one statement. Please help.

我尝试使用 DISTINCT 但它在一个语句中不适用于 ORDER BY。请帮忙。

Important:

重要的：

1. I tried it with:

   SELECT DISTINCT Category FROM MonitoringJob ORDER BY CreationDate DESC
   

   it doesn't work.

2. Order by CreationDate is very important.

1. 我试过：

   SELECT DISTINCT Category FROM MonitoringJob ORDER BY CreationDate DESC
   

   它不起作用。

2. 按 CreationDate 排序非常重要。

回答by Prutswonder

The problem is that the columns used in the ORDER BYaren't specified in the DISTINCT. To do this, you need to use an aggregate functionto sort on, and use a GROUP BYto make the DISTINCTwork.

问题是 中使用的列ORDER BY未在DISTINCT. 为此，您需要使用聚合函数进行排序，并使用 aGROUP BY进行DISTINCT工作。

Try something like this:

尝试这样的事情：

SELECT DISTINCT Category, MAX(CreationDate) 
FROM MonitoringJob 
GROUP BY Category 
ORDER BY MAX(CreationDate) DESC, Category

回答by Lukas Eder

Extended sort key columns

扩展排序键列

The reason why what you want to do doesn't work is because of the logical order of operations in SQL, which, for your first query, is (simplified):

您想要执行的操作不起作用的原因是因为SQL 中操作的逻辑顺序，对于您的第一个查询，它是（简化的）：

• FROM MonitoringJob
• SELECT Category, CreationDatei.e. add a so called extended sort key column
• ORDER BY CreationDate DESC
• SELECT Categoryi.e. remove the extended sort key columnagain from the result.

• FROM MonitoringJob
• SELECT Category, CreationDate即添加一个所谓的扩展排序键列
• ORDER BY CreationDate DESC
• SELECT Category即再次从结果中删除扩展排序键列。

So, thanks to the SQL standard extended sort key columnfeature, it is totally possible to order by something that is not in the SELECTclause, because it is being temporarily added to it behind the scenes.

因此，由于 SQL 标准扩展排序键列功能，完全可以按SELECT子句中没有的内容进行排序，因为它是在幕后临时添加的。

So, why doesn't this work with DISTINCT?

那么，为什么这不适用于DISTINCT？

If we add the DISTINCToperation, it would be added between SELECTand ORDER BY:

如果我们添加DISTINCT操作，它将被添加到SELECT和之间ORDER BY：

• FROM MonitoringJob
• SELECT Category, CreationDate
• DISTINCT
• ORDER BY CreationDate DESC
• SELECT Category

• FROM MonitoringJob
• SELECT Category, CreationDate
• DISTINCT
• ORDER BY CreationDate DESC
• SELECT Category

But now, with the extended sort key columnCreationDate, the semantics of the DISTINCToperation has been changed, so the result will no longer be the same. This is not what we want, so both the SQL standard, and all reasonable databases forbid this usage.

但是现在，随着排序键列的扩展CreationDate，DISTINCT操作的语义发生了变化，因此结果将不再相同。这不是我们想要的，所以 SQL 标准和所有合理的数据库都禁止这种用法。

Workarounds

解决方法

It can be emulated with standard syntax as follows

它可以用标准语法模拟如下

SELECT Category
FROM (
  SELECT Category, MAX(CreationDate) AS CreationDate
  FROM MonitoringJob
  GROUP BY Category
) t
ORDER BY CreationDate DESC

Or, just simply (in this case), as shown also by Prutswonder

或者，只是简单地（在这种情况下），如Prutswonder所示

SELECT Category, MAX(CreationDate) AS CreationDate
FROM MonitoringJob
GROUP BY Category
ORDER BY CreationDate DESC

I have blogged about SQL DISTINCT and ORDER BY more in detail here.

我在博客上更详细地介绍了 SQL DISTINCT 和 ORDER BY。

回答by Marc_Sei

If the output of MAX(CreationDate) is not wanted - like in the example of the original question - the only answer is the second statement of Prashant Gupta's answer:

如果不需要 MAX(CreationDate) 的输出 - 就像在原始问题的示例中一样 - 唯一的答案是 Prashant Gupta 答案的第二个陈述：

SELECT [Category] FROM [MonitoringJob] 
GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC

Explanation: you can't use the ORDER BY clause in an inline function, so the statement in the answer of Prutswonder is not useable in this case, you can't put an outer select around it and discard the MAX(CreationDate) part.

说明：你不能在内联函数中使用 ORDER BY 子句，所以在这种情况下，Prutswonder 的答案中的语句是不可用的，你不能在它周围放置一个外部选择并丢弃 MAX(CreationDate) 部分。

回答by Prashant Gupta

Just use this code, If you want values of [Category] and [CreationDate] columns

只需使用此代码，如果您想要 [Category] ​​和 [CreationDate] 列的值

SELECT [Category], MAX([CreationDate]) FROM [MonitoringJob] 
             GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC

Or use this code, If you want only values of [Category] column.

或者使用此代码，如果您只需要 [Category] ​​列的值。

SELECT [Category] FROM [MonitoringJob] 
GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC

You'll have all the distinct records what ever you want.

您将拥有您想要的所有独特记录。

回答by JohnSurrey

2) Order by CreationDate is very important

2）按CreationDate排序很重要

The original results indicated that "test3" had multiple results...

原来的结果表明“test3”有多个结果......

It's very easy to start using MAX all the time to remove duplicates in Group By's... and forget or ignore what the underlying question is...

一直开始使用 MAX 来删除 Group By 中的重复项非常容易……忘记或忽略潜在的问题是什么……

The OP presumably realised that using MAX was giving him the last "created" and using MIN would give the first "created"...

OP 大概意识到使用 MAX 会给他最后一个“创建”，而使用 MIN 会给他第一个“创建”......

回答by Bob

if object_id ('tempdb..#tempreport') is not null
begin  
drop table #tempreport
end 
create table #tempreport (
Category  nvarchar(510),
CreationDate smallint )
insert into #tempreport 
select distinct Category from MonitoringJob (nolock) 
select * from #tempreport  ORDER BY CreationDate DESC

回答by Shiwangini

By subquery, it should work:

通过子查询，它应该可以工作：

    SELECT distinct(Category) from MonitoringJob  where Category in(select Category from MonitoringJob order by CreationDate desc);

回答by Jair

You can use CTE:

您可以使用 CTE：

WITH DistinctMonitoringJob AS (
    SELECT DISTINCT Category Distinct_Category FROM MonitoringJob 
)

SELECT Distinct_Category 
FROM DistinctMonitoringJob 
ORDER BY Distinct_Category DESC

回答by C Patel

Distinct will sort records in ascending order. If you want to sort in desc order use:

Distinct 将按升序对记录进行排序。如果要按降序排序，请使用：

SELECT DISTINCT Category
FROM MonitoringJob
ORDER BY Category DESC

If you want to sort records based on CreationDate field then this field must be in the select statement:

如果要根据 CreationDate 字段对记录进行排序，则此字段必须在 select 语句中：

SELECT DISTINCT Category, creationDate
FROM MonitoringJob
ORDER BY CreationDate DESC

回答by Má?a - Stitod.cz

Try next, but it's not useful for huge data...

试试下一个，但它对大量数据没有用......

SELECT DISTINCT Cat FROM (
  SELECT Category as Cat FROM MonitoringJob ORDER BY CreationDate DESC
);