How to use DISTINCT and ORDER BY in same SELECT statement?
SqlSql Order-ByDistinctSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
The problem is that the columns used in the ORDER BY
aren't specified in the DISTINCT
. To do this, you need to use an aggregate function to sort on, and use a GROUP BY
to make the DISTINCT
work.
Try something like this:
SELECT DISTINCT Category, MAX(CreationDate)
FROM MonitoringJob
GROUP BY Category
ORDER BY MAX(CreationDate) DESC, Category
Solution 2 - Sql
Extended sort key columns
The reason why what you want to do doesn't work is because of the logical order of operations in SQL, which, for your first query, is (simplified):
FROM MonitoringJob
SELECT Category, CreationDate
i.e. add a so called extended sort key columnORDER BY CreationDate DESC
SELECT Category
i.e. remove the extended sort key column again from the result.
So, thanks to the SQL standard extended sort key column feature, it is totally possible to order by something that is not in the SELECT
clause, because it is being temporarily added to it behind the scenes.
DISTINCT
?
So, why doesn't this work with If we add the DISTINCT
operation, it would be added between SELECT
and ORDER BY
:
FROM MonitoringJob
SELECT Category, CreationDate
DISTINCT
ORDER BY CreationDate DESC
SELECT Category
But now, with the extended sort key column CreationDate
, the semantics of the DISTINCT
operation has been changed, so the result will no longer be the same. This is not what we want, so both the SQL standard, and all reasonable databases forbid this usage.
Workarounds
It can be emulated with standard syntax as follows
SELECT Category
FROM (
SELECT Category, MAX(CreationDate) AS CreationDate
FROM MonitoringJob
GROUP BY Category
) t
ORDER BY CreationDate DESC
Or, just simply (in this case), as shown also by Prutswonder
SELECT Category, MAX(CreationDate) AS CreationDate
FROM MonitoringJob
GROUP BY Category
ORDER BY CreationDate DESC
I have blogged about SQL DISTINCT and ORDER BY more in detail here.
Solution 3 - Sql
If the output of MAX(CreationDate) is not wanted - like in the example of the original question - the only answer is the second statement of Prashant Gupta's answer:
SELECT [Category] FROM [MonitoringJob]
GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC
Explanation: you can't use the ORDER BY clause in an inline function, so the statement in the answer of Prutswonder is not useable in this case, you can't put an outer select around it and discard the MAX(CreationDate) part.
Solution 4 - Sql
Just use this code, If you want values of [Category] and [CreationDate] columns
SELECT [Category], MAX([CreationDate]) FROM [MonitoringJob]
GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC
Or use this code, If you want only values of [Category] column.
SELECT [Category] FROM [MonitoringJob]
GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC
You'll have all the distinct records what ever you want.
Solution 5 - Sql
> 2) Order by CreationDate is very important
The original results indicated that "test3" had multiple results...
It's very easy to start using MAX all the time to remove duplicates in Group By's... and forget or ignore what the underlying question is...
The OP presumably realised that using MAX was giving him the last "created" and using MIN would give the first "created"...
Solution 6 - Sql
if object_id ('tempdb..#tempreport') is not null
begin
drop table #tempreport
end
create table #tempreport (
Category nvarchar(510),
CreationDate smallint )
insert into #tempreport
select distinct Category from MonitoringJob (nolock)
select * from #tempreport ORDER BY CreationDate DESC
Solution 7 - Sql
Distinct will sort records in ascending order. If you want to sort in desc order use:
SELECT DISTINCT Category
FROM MonitoringJob
ORDER BY Category DESC
If you want to sort records based on CreationDate field then this field must be in the select statement:
SELECT DISTINCT Category, creationDate
FROM MonitoringJob
ORDER BY CreationDate DESC
Solution 8 - Sql
You can use CTE:
WITH DistinctMonitoringJob AS (
SELECT DISTINCT Category Distinct_Category FROM MonitoringJob
)
SELECT Distinct_Category
FROM DistinctMonitoringJob
ORDER BY Distinct_Category DESC
Solution 9 - Sql
By subquery, it should work:
SELECT distinct(Category) from MonitoringJob where Category in(select Category from MonitoringJob order by CreationDate desc);
Solution 10 - Sql
Try next, but it's not useful for huge data...
SELECT DISTINCT Cat FROM (
SELECT Category as Cat FROM MonitoringJob ORDER BY CreationDate DESC
);
Solution 11 - Sql
It can be done using inner query Like this
$query = "SELECT *
FROM (SELECT Category
FROM currency_rates
ORDER BY id DESC) as rows
GROUP BY currency";
Solution 12 - Sql
SELECT DISTINCT Category FROM MonitoringJob ORDER BY Category ASC