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.

Answer 2

You’re attempting to order the results of your query by CreationDate and check different categories from the MonitoringJob database in the same query, but resulting error because CreationDate is not included in one of the queries in the SELECT statement.

SELECT DISTINCT Category

FROM MonitoringJob

WHERE CreationDate IN (

    SELECT MAX(CreationDate)

    FROM MonitoringJob

    GROUP BY Category

)

ORDER BY CreationDate DESC;

In the first place, we establish a specific last document date for each category by selecting from a subquery for every category. Next, the outer query deals with the remaining dates and in the end order it.

Answer 3

A) In the first phase, the objectives of the project and related data sources are determined by data scientists. This phase is critical to ensure goals are clear and a proper understanding of the problem exists.

B) Here, in the second phase, the methods are chosen, and the roadmap for analysis is established by the data scientist. The techniques to be adopted for modeling and data requirement have to be decided to properly address the problem.

C) Communication Building: It is not a traditional phase of Data Science. Even though results need to be communicated, "Communication Building" is not a phase in and of itself.

D) Operationalize: It is when the model goes to production so that it could be utilized in the real world. It helps the model function on real-time data and to give constant insights.