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;