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: 
```
select t.id, t.value
from TABLE t
inner join 
(
  SELECT id, count(*) as cnt 
  FROM TABLE 
  group by ID
)
x on x.id = t.id
order by x.cnt desc

```
I see the question is already answered, but since the most obvious and most simple solution is missing, I'm posting it anyway. It doesn't use self joins nor subqueries: 

```
SQL> create table t (id,value)
  2  as
  3  select 1, 'hi' from dual union all
  4  select 1, 'yo' from dual union all
  5  select 2, 'foo' from dual union all
  6  select 2, 'bar' from dual union all
  7  select 2, 'hehe' from dual union all
  8  select 3, 'ha' from dual union all
  9  select 6, 'gaga' from dual
 10  /

Table created.

SQL> select id
  2       , value
  3    from t
  4   order by count(*) over (partition by id) desc
  5  /

        ID VALU
---------- ----
         2 bar
         2 hehe
         2 foo
         1 yo
         1 hi
         6 gaga
         3 ha

7 rows selected.

```