Home > Database > Mysql Tutorial > How to Get Unique Sorted Results Using DISTINCT and ORDER BY in SQL?

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

Patricia Arquette
Release: 2025-01-07 09:26:40
Original
426 people have browsed it

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
Copy after login

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
Copy after login

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
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template