Query to get distinct items at the top of results and duplicates following

Question

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

Answer 1

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:

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

Another way replaces ROW_NUMBER with

 COUNT(*)
 Over (PARTITION BY Item
 ROWS UNBOUNDED PRECEDING) AS rn

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):

 ROW_NUMBER() 
 Over (PARTITION BY Item
 ORDER BY (SELECT 1)) AS rn

Answer 2

About the same as dnoeth +1

create table Items(Id INT,Item VARCHAR(5))
insert into Items values
 (1,'Cat')
, (2,'Dog')
, (3,'Dog')
, (4,'Cat')
, (5,'Fish')
, (6,'Cat')
, (7,'Dog')
;
with cte as 
( select Id, Item 
 , ROW_NUMBER() over (partition by item order by Id) as rn
 from Items
)
select Id, Item
from cte
order by rn;

• paparazzo: Down vote what specifically is the problem with the difference?
• dnoeth: Maybe he/she didn't notice that you changed the ORDER BY to better match the OP's result.