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

本文介绍了如何在同一个SELECT语句中使用DISTINCT和ORDER BY？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

执行以下语句后：

从MonitoringJob ORDER BY CreationDate DESC

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

test3 test3 bildung test4 test3 test2 test1

，但我希望删除重复项，例如：

bildung test4 test3 test2 test1

使用DISTINCT，但在一个语句中不能与ORDER BY一起使用。

重要提示：

我尝试过：

从MonitoringJob ORDER BY CreationDate DESC

它不起作用。

通过CreationDate进行排序非常重要。

解决方案

问题是 ORDER BY 。为此，您需要使用集合函数进行排序，并使用 GROUP BY 使 DISTINCT 起作用。

尝试类似这样的事情：

SELECT DISTINCT类别，MAX（Cre​​ationDate）从MonitoringJob 按类别分组，按MAX（Cre​​ationDate）DESC排序，类别

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.

Important:

I tried it with:

SELECT DISTINCT Category FROM MonitoringJob ORDER BY CreationDate DESC

it doesn't work.

Order by CreationDate is very important.

解决方案

The problem is that the columns used in the ORDER BY aren't specified in the DISTINCT. To do this, you need to use an aggregate function to sort on, and use a GROUP BY to make the DISTINCT work.

Try something like this:

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