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

sql sql-order-by distinct
r.r picture r.r · Mar 22, 2011 · Viewed 314.2k times · Source

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

Prutswonder picture Prutswonder · Mar 22, 2011

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

Related questions

Using distinct on a column and doing order by on another column gives an error

I have a table: abc_test with columns n_num, k_str. This query doesnt work: select distinct(n_num) from abc_test order by(k_str) But this one works: select n_num from abc_test order by(k_…

Read More
SQL multiple column ordering

I am trying to sort by multiple columns in SQL, and in different directions. column1 would be sorted descending, and column2 ascending. How can I do this?

Read More
How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More