sql - 如何同时选择 count(*) group by 和 select *？

Question

例如，我有表:

ID   |   Value
1         hi
1         yo
2         foo
2         bar
2         hehe
3         ha
6         gaga

我希望我的查询获得 ID、值；同时返回的集合应该按照每个ID的频率计数顺序。

我尝试了下面的查询，但不知道如何同时获取 ID 和 Value 列:

SELECT COUNT(*) FROM TABLE group by ID order by COUNT(*) desc;

计数数字对我来说无关紧要，我只需要数据按此顺序排列即可。

愿望结果:

ID   |   Value

2         foo
2         bar
2         hehe
1         hi
1         yo
3         ha
6         gaga

As you can see because ID:2 appears most times(3 times), it's first on the list, 
then ID:1(2 times) etc. 

Answer 1

你可以试试这个——

    select id, value, count(*) over (partition by id)  freq_count
from
(
select 2  as ID,  'foo' as value
from dual
union all
select 2,         'bar'
from dual
union all
select 2,         'hehe'
from dual
union all
select 1 ,        'hi'
from dual
union all
select 1  ,       'yo'
from dual
union all
select 3   ,      'ha'
from dual
union all
select 6    ,     'gaga'
from dual
)
order by 3 desc;