如何在同一个SELECT语句中使用DISTINCT和ORDER BY？ [英] How to use DISTINCT and ORDER BY in same SELECT statement?

问题描述

执行以下语句后：

 从MonitoringJob ORDER BY CreationDate DESC 
  

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

  test3 
 test3 
 bildung 
 test4 
 test3 
 test2 
 test1 
  

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

  bildung 
 test4 
 test3 
 test2 
 test1 
  

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

重要提示：

1. 我尝试过：

    从MonitoringJob ORDER BY CreationDate DESC 
      
    
    
   它不起作用。
    




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

解决方案

问题是 ORDER BY DISTINCT 中未指定c $ c>。为此，您需要使用集合函数进行排序，并使用 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:

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.

解决方案

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

这篇关于如何在同一个SELECT语句中使用DISTINCT和ORDER BY？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！