How Can I Remove Duplicate Rows While Maintaining Order in a SQL SELECT Statement?

Using DISTINCT and ORDER BY in the Same SELECT Statement

After executing a SELECT statement retrieving the Category column from the MonitoringJob table and ordering the results by the CreationDate column in descending order, the database may return duplicate values. To eliminate duplicates while maintaining the specified order, the DISTINCT keyword can be utilized. However, using DISTINCT alone may not suffice.

In the provided example, using the DISTINCT keyword by itself won't resolve the issue because the ORDER BY clause relies on the CreationDate column, which contains duplicate values. To address this, an aggregate function such as MAX() can be combined with the DISTINCT keyword to sort the results based on a specific column while still excluding duplicates. Additionally, a GROUP BY clause is necessary to create unique categories based on the column used in the DISTINCT operation.

An appropriate solution would be:

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

This modified statement will ensure that duplicates are removed while preserving the desired order by sorting on the MAX(CreationDate) column and then by Category. As a result, the desired output will be obtained, listing the distinct categories in descending order based on their latest CreationDate values.

The above is the detailed content of How Can I Remove Duplicate Rows While Maintaining Order in a SQL SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!