How to Get Unique Sorted Results Using DISTINCT and ORDER BY in SQL?

Combining DISTINCT and ORDER BY for Unique Results

When faced with a dataset containing duplicate values, using DISTINCT to remove duplicates can be essential for obtaining unique results. However, when sorting is also required, using ORDER BY in the same SELECT statement can pose a challenge.

To address this issue, consider the following query:

SELECT  Category  FROM MonitoringJob ORDER BY CreationDate DESC

This query retrieves data from the MonitoringJob table, ordering the results in descending order by the CreationDate column. The desired outcome is a list of unique categories, sorted by the latest creation date. However, the query produces duplicate values.

One attempt to resolve this issue might be using DISTINCT:

SELECT DISTINCT Category FROM MonitoringJob ORDER BY CreationDate DESC

Unfortunately, this query fails because DISTINCT does not work with ORDER BY in a single statement.

To resolve this issue, an aggregate function and GROUP BY clause can be used:

SELECT DISTINCT Category, MAX(CreationDate) 
FROM MonitoringJob 
GROUP BY Category 
ORDER BY MAX(CreationDate) DESC, Category

This query:

1. Uses the MAX() aggregate function to find the latest creation date for each category.
2. Employs GROUP BY to group the results by category, ensuring unique values.
3. Sorts the results first by the latest creation date (descending order) and then by category.

This approach combines the power of DISTINCT to eliminate duplicates with the functionality of ORDER BY to sort the data, giving you unique results sorted in the desired order.

The above is the detailed content of How to Get Unique Sorted Results Using DISTINCT and ORDER BY in SQL?. For more information, please follow other related articles on the PHP Chinese website!