CODEWITHSUNDEEP
mysql
lucky
lucky

Reputation: 39

using order by and group by together in mysql

there is table called sample with columns name and games.

name      games  
a        tennis  
b        tennis  
c        football  
d        shuttle  
e        basketball  
f        football  
g        football

and so on.

I need the list of fisrt 2 most played game and first 2 least played game. how to use group by and order by together?

Upvotes: 1

Views: 95

Answers (2)

rocks
rocks

Reputation: 190

select games, count(1) value
from sample
group by games order by value desc limit 2
UNION ALL
select games, count(1) value
from sample
group by games order by value limit 2

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

If you want a count ordered you can do this 

select count(*)  as num, games from sample 
group by games 
order by  num

for the firts two 

select count(*)   as num, games from sample 
group by games 
order by  num limit 2

for the last two 

select count(*)  as num, games from sample 
group by games 
order by  num DESC limit 2

Upvotes: 2

Related Questions