如何在同一个SELECT语句中使用不同的顺序?

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.

   按生产日期订货是很重要的。

10 个解决方案

#1

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.

问题是，ORDER BY中使用的列没有在不同的列中指定。要做到这一点，您需要使用聚合函数进行排序，并使用GROUP BY来完成不同的工作。

Try something like this:

试试这样:

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

#2

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)的输出(如原始问题的示例)，唯一的答案就是普瑞森特·古普塔的第二句回答:

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)部分。

#3

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.

你想要什么就有什么。

#4

2) Order by CreationDate is very important

按创建日期排序是非常重要的

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将会给第一个“创建”…

#5

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

Distinct将按升序排序记录。如果您想按desc顺序排序，请使用:

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

#6

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

#7

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

#8

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

试试next，但是对大数据没用……

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

#9

It can be done using inner query Like this

可以像这样使用内部查询

$query = "SELECT * 
            FROM (SELECT Category  
                FROM currency_rates                 
                ORDER BY id DESC) as rows               
            GROUP BY currency";

#10

SELECT DISTINCT Category FROM MonitoringJob ORDER BY Category ASC