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:

I tried it with:

SELECT DISTINCT Category FROM MonitoringJob ORDER BY CreationDate DESC

it doesn't work.

Order by CreationDate is very important.

Answer 1

The columns used in the ORDER BY aren't specified in the DISTINCT. An aggregate function must be used to sort on, and use the GROUP BY to make the DISTINCT work.

Try using the below query:

SELECT DISTINCT Category, MAX(CreationDate) 

FROM MonitoringJob 

GROUP BY Category 

ORDER BY MAX(CreationDate) DESC, Category

Comments

Great solution, thanks for the help.