Ordering and counting dimension keys and sort by another key

Question

Assuming a table like this:

xid	yid	otherStuff
1000	100	Three
1000	101	Car
1001	100	Flower
1001	100	Flower
1000	100	Three
1002	101	Bus
1003	101	Train
1002	100	Bee
1001	102	Iron
1002	102	Gold
1003	102	Silver
1001	102	Iron
1000	100	Three

I would like to return the xids ordered by count(*). The order should be first the xids where yid = 100 then where yid != 100

So the result should be

1000 (because yid = 100, count(*) = 3 )
1001 (because yid = 100, count(*) = 2 )
1002 (because yid = 100, count(*) = 1 )
1003 (because yid != 100, count(*) = 2 (even yid !=yid)

Every ordered xid should only appear one single time. My approach does not return the single ids it repeats the xids

SELECT * FROM (
SELECT [xid], 
  [yid],
  count(1) as cnt 
FROM [fbfact].[Journal]
where [yid] = 1000 
group by [xid],[yid]
UNION
SELECT [xid],
   [yid],
   count(1) as cnt
FROM [fbfact].[Journal]
where [yid] != 1000
group by [xid],[yid] ) as x

I can't get my head around this.

Answer 1

Another option, you could use a conditional count function as the following:

select xid
from table_name
group by xid
order by count(case when yid = 100 then 1 end) desc,
         count(case when yid <> 100 then 1 end) desc

demo