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
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, Dog