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

SherylHohman
– SherylHohman

2019-11-28 19:58:53 +00:00
Commented Nov 28, 2019 at 19:58 — SherylHohman
– SherylHohman, Commented Nov 28, 2019 at 19:58

ScaisEdge · Accepted Answer · 2019-11-28 20:00:30Z

3

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

answered Nov 28, 2019 at 20:00

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2019-11-28 20:05:54Z

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, 2019 at 20:05

GMB

224k25 gold badges102 silver badges151 bronze badges

Comments

Amjad Paracha · Accepted Answer · 2019-11-28 20:03:45Z

-4

Use order by category.

select * from [table] order by category

answered Nov 28, 2019 at 20:03

Amjad Paracha

111 silver badge3 bronze badges

1 Comment

Ashley Kilgour Over a year ago

This will just order them, but will not put the one that happens the most at the top

Collectives™ on Stack Overflow

SQL ORDER BY GROUP

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related