patternsqlMinor
Query to get distinct items at the top of results and duplicates following
Viewed 0 times
distinctthetopqueryfollowinggetitemsandresultsduplicates
Problem
I need to query the table below and list the distinct items at the top of my result set and the duplicates will follow, the order of the duplicates does not matter.
I am working with Sql Server 2016 if that offers any nice tricks for improvement.
Table design
Here is my current query. It does function correctly but seems very poor and brute force'ish.
Results
I am working with Sql Server 2016 if that offers any nice tricks for improvement.
Table design
create table Items(Id INT,Item VARCHAR(5))
insert into Items values(1,'Cat')
insert into Items values(2,'Dog')
insert into Items values(3,'Dog')
insert into Items values(4,'Cat')
insert into Items values(5,'Fish')
insert into Items values(6,'Cat')
insert into Items values(7,'Dog')Here is my current query. It does function correctly but seems very poor and brute force'ish.
--Select all the items but have the distinct ones appear at the top and repeats following
SELECT Id= (SELECT TOP 1 Id FROM Items it2 WHERE it2.Item = it1.Item ORDER BY Id ASC), Item
FROM Items it1
GROUP BY Item
UNION ALL
SELECT *
FROM Items WHERE Items.Id NOT IN
(
SELECT Id= (SELECT TOP 1 Id FROM Items it2 WHERE it2.Item = it1.Item ORDER BY Id ASC)
FROM Items it1
GROUP BY Item
)Results
1, Cat
2, Dog
5, Fish
3, Dog
4, Cat
6, Cat
7, DogSolution
That's a simple task for Windowed Aggregates :-)
Assign a sequential number (1,2,3,...) to each row with the same Item and order by that number:
Another way replaces ROW_NUMBER with
This should be more efficient as it doesn't need to actually sort.
I can't test it right now, but it should be similar to a trick used in older versions of SQL Server (before Cumulative aggregates have been implemented):
Assign a sequential number (1,2,3,...) to each row with the same Item and order by that number:
WITH cte AS
(
SELECT Id, Item,
ROW_NUMBER()
Over (PARTITION BY Item
ORDER BY Item) AS rn
FROM Items
)
SELECT Id, Item
FROM cte
ORDER BY rn, ItemAnother way replaces ROW_NUMBER with
COUNT(*)
Over (PARTITION BY Item
ROWS UNBOUNDED PRECEDING) AS rnThis should be more efficient as it doesn't need to actually sort.
I can't test it right now, but it should be similar to a trick used in older versions of SQL Server (before Cumulative aggregates have been implemented):
ROW_NUMBER()
Over (PARTITION BY Item
ORDER BY (SELECT 1)) AS rnCode Snippets
WITH cte AS
(
SELECT Id, Item,
ROW_NUMBER()
Over (PARTITION BY Item
ORDER BY Item) AS rn
FROM Items
)
SELECT Id, Item
FROM cte
ORDER BY rn, ItemCOUNT(*)
Over (PARTITION BY Item
ROWS UNBOUNDED PRECEDING) AS rnROW_NUMBER()
Over (PARTITION BY Item
ORDER BY (SELECT 1)) AS rnContext
StackExchange Code Review Q#155030, answer score: 3
Revisions (0)
No revisions yet.