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

Question

After executing the following statement:

SELECT Category FROM MonitoringJob ORDER BY CreationDate DESC 

I get the following values ​​from the database:

 test3 test3 bildung test4 test3 test2 test1 

but I want duplicates to be removed, for example:

 bildung test4 test3 test2 test1 

I tried using DISTINCT, but it does not work with ORDER BY in a single expression. Please, help.

Important:

• I tried:

   SELECT DISTINCT Category FROM MonitoringJob ORDER BY CreationDate DESC 

  he does not work.

• Ordering by CreationDate is very important.

Answer 1

The problem is that the columns used in ORDER BY are not specified in DISTINCT . To do this, you need to use the aggregate function to sort and use GROUP BY to get DISTINCT to work.

Try something like this:

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

Answer 2

Extended Sort Key Columns

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

• FROM MonitoringJob
• SELECT Category, CreationDate i.e. add the so-called extended sort key column
• ORDER BY CreationDate DESC
• SELECT Category i.e. remove the extended collation key column from the result again.

Thus, thanks to the standard function of the extended column of the SQL collation key, you can completely order something that is not in the SELECT because it is temporarily added to it behind the scenes.

So why does this not work with DISTINCT ?

If we add a DISTINCT operation, it will be added between SELECT and ORDER BY :

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

But now, with the CreationDate extended collation key column, the semantics of the DISTINCT operation have been changed, so the result will no longer be the same. This is not what we want, so the SQL standard and all reasonable databases prohibit this use.

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 (in this case) as shown by Prutswonder

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

I wrote more about SQL DISTINCT and ORDER BY here .

Answer 3

If the output of MAX (CreationDate) is not needed - as in the example of the original question - the only answer is the second response statement by Prashant Gupta:

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

Explanation: you cannot use the ORDER BY clause in a built-in function, therefore the statement in the Prutswonder answer cannot be used in this case, you cannot put an external selection around it and cancel the MAX (CreationDate) part.

Answer 4

Just use this code. If you need the values ​​of the [Category] and [CreationDate] columns

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

Or use this code if you want only the values ​​of the [Category] column.

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

You will have all the records you want.

Answer 5

2) Ordering by CreationDate is very important

The initial results showed that "test3" has several results ...

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

The OP allegedly realized that using MAX would give it the last "created", and using MIN would give the first "created" ...

Answer 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 

Answer 7

By subquery should work:

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

Answer 8

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

 SELECT DISTINCT Category FROM MonitoringJob ORDER BY Category DESC 

If you want to sort the entries based on the CreationDate field, this field should be in the select statement:

 SELECT DISTINCT Category, creationDate FROM MonitoringJob ORDER BY CreationDate DESC 

Answer 9

You can use CTE:

 WITH DistinctMonitoringJob AS ( SELECT DISTINCT Category Distinct_Category FROM MonitoringJob ) SELECT Distinct_Category FROM DistinctMonitoringJob ORDER BY Distinct_Category DESC 

Answer 10

Try the following, but it is not useful for huge data ...

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

Answer 11

This can be done using an internal query. Like this

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

Answer 12

 SELECT DISTINCT Category FROM MonitoringJob ORDER BY Category ASC