Dan Dan - 4 months ago 23
SQL Question

sql - order by for each group

i have a query that looks like this:

SELECT
USER .id,
listing.country,
count(listing.id) AS count
FROM USER
INNER JOIN listing ON USER.id = listing.user_id
GROUP BY listing.country, USER.id
ORDER BY count DESC;


and the result i get looks like this:

enter image description here

however i'd like it to be grouped by country so that the users can be ranked per country by their number of listings. is there a way to fix this?

Answer

You can have multiple ORDER BY columns:

SELECT `user`.`id`, 
       `listing`.`country`,
       count(`listing`.`id`) as `count` 
FROM `user` 
INNER JOIN `listing` ON (`user`.`id` = `listing`.`user_id`) 
GROUP BY `listing`.`country,` `user`.`id` 
ORDER BY `country`, `count` DESC;