DiegoSahagun DiegoSahagun - 7 months ago 24
SQL Question

SQL Sort by group size

I want to make a select query which groups rows based on a given column and then sorts by size of such groups.

Let's say we have this sample data:

id type
1 c
2 b
3 b
4 a
5 c
6 b


I want to obtain the following by grouping and sorting the column 'type' in a descending way:

id type
2 b
3 b
6 b
1 c
5 c
4 a


As of now I am only able to get the count of each group but that is not exactly what I need:

SELECT *, COUNT(type) AS typecount
FROM sampletable
GROUP BY type
ORDER BY typecount DESC, type ASC

id type count
2 b 3
1 c 2
4 a 1


Can anybody please give me a hand with this query?

Edit:
Made 'b' the biggest group to avoid coming to the same solution by using only SORT BY

Answer

It may not be the best way, but it will give you what you want.

You work out the totals for each group and then join that "virtual" table to your original table by the determined counts.

SELECT *
FROM sampletable s1
INNER JOIN (SELECT count(type) AS iCount,type
        FROM sampletable
        GROUP BY type) s2 ON s2.type = s1.type
ORDER BY s2.iCount DESC, s1.type ASC

http://sqlfiddle.com/#!9/f6b0c4/6/0