SQL ORDER BY GROUP

Question

question category 
A           X
B           Y
C           X
D           X
E           Y

I want to make the output from the most frequent category

question category
A             X
C             X
D             X
B             Y
E             Y

Possible duplicate of [MySQL order by before group by](https://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by) — SherylHohman, Nov 28 '19 at 19:58

score 3 · Answer 1 · answered Nov 28 '19 at 20:00

You could try with join on the count for category

select m.question, m.category  
from my_table  m
inner join  (
  select category, count(*) num
  from my_table  
   group by category 
) t on t.category = m.category
order by t.num desc, m.category, m.question

score 2 · Answer 2 · answered Nov 28 '19 at 20:05

2

If you are running MySQL 8.0, you can use window functions for this. This avoids the need for a join.

select *
from mytable
order by 
    count(*) over(partition by category) desc,
    question

answered Nov 28 '19 at 20:05

GMB

216,147
25
84
135

score -4 · Answer 3 · answered Nov 28 '19 at 20:03

-4

Use order by category.

select * from [table] order by category

answered Nov 28 '19 at 20:03

Amjad Paracha

11
1
3

This will just order them, but will not put the one that happens the most at the top – Ashley Kilgour Nov 28 '19 at 20:29

SQL ORDER BY GROUP

3 Answers3