Question: For example, I have table:

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

```

I want my query to get ID, Value; meanwhile the returned set should be in the order of frequency count of each ID.

I tried the query below but don't know how to get the ID and Value column at the same time:

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

```

The count number doesn't matter to me, I just need the data to be in such order.

Desire Result:

```
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: 
you can try this - 

```
    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;

```
How about something like

```
SELECT  t.ID,
        t.Value,
        c.Cnt
FROM    TABLE t INNER JOIN
        (
            SELECT  ID,
                    COUNT(*) Cnt
            FROM    TABLE
            GROUP BY ID
        ) c ON  t.ID = c.ID
ORDER BY c.Cnt DESC

```

[SQL Fiddle DEMO](http://www.sqlfiddle.com/#!2/3fffe/2)
-------------------------------------------------------