Advertisement
Advertisement


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


Question

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.

2015/07/18
1
118
7/18/2015 12:39:48 AM

Accepted Answer

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
2017/12/28
196
12/28/2017 3:48:04 PM

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):

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

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

So, why doesn't this work with DISTINCT?

If we add the DISTINCT operation, it would be added between SELECT and ORDER BY:

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

But now, with the extended sort key column CreationDate, the semantics of the DISTINCT operation 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.

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

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.

2019/05/17

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:

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.

2016/12/16

Just use this code, If you want values of [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 values of [Category] column.

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

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

2013/01/30

2) Order by CreationDate is very important

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

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...

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

2013/10/22

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
2017/01/25

Source: https://stackoverflow.com/questions/5391564
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]