r.r r.r - 7 days ago 6
SQL Question

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

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.


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
Comments