SQL Server Ordering and counting dimension keys and sort by another key

3npbholx 于 2023-02-28 发布在其他

关注(0)|答案(3)|浏览(82)

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.

sql-server

来源：https://stackoverflow.com/questions/75526006/ordering-and-counting-dimension-keys-and-sort-by-another-key

3条答案

按热度按时间

km0tfn4u1#

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

赞(0）回复(0）举报 2023-02-28

n7taea2i2#

You can do:

select xid
from (
  select xid, count(*) as cnt,
    max(case when yid = 100 then 1 else 0 end) as has_100
  from [fbfact].[Journal]
  group by xid
) x
order by has_100 desc, cnt desc

赞(0）回复(0）举报 2023-02-28

oxf4rvwz3#

You could order by a windowed count,

select xid
from (
  select distinct xid, Count(case when yid = 100 then xid end) over(partition by xid) o
  from fbfact.Journal
)x
order by o desc;

赞(0）回复(0）举报 2023-02-28

我来回答

SQL Server Ordering and counting dimension keys and sort by another key

3条答案

相关问题

热门标签

最新问答