CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 323

MySQL order by number of occuring columns

I have a table like this

|   id  |   name    |
----------------------
|   A   |   test a1 |
|   A   |   test a2 |
|   A   |   test a3 |
|   B   |   test b1 |
|   B   |   test b2 |
|   A   |   test a4 |
|   C   |   test c1 |

Id A has the most values, then B, etc. How can I return rows ordered by number of names that is associated with id. I tried this but since I group by id I lose all names that are associated with the id.

SELECT id, name, COUNT(name) as name_count 
FROM users 
GROUP BY id 
ORDER BY name_count DESC

Upvotes: 0

Views: 77

Answers (2)

Radim Bača
Radim Bača

Reputation: 10711

It can be solved even without subquery if there is one column that is unique in users table. For example, if name is unique then it will be as follows:

SELECT u1.id, u1.name, COUNT(*) as name_count 
FROM users u1
JOIN users u2 ON u1.id = u2.id
GROUP BY u1.name
ORDER BY name_count DESC

Upvotes: 0

Andrew
Andrew

Reputation: 1594

You're close. To get around losing the names, you need to join a subquery to handle the counting: 

SELECT u.id, u.name
FROM users u
JOIN (
  SELECT id, count(1) cnt
  FROM users
  GROUP BY id) ss 
ON u.id = ss.id
ORDER BY ss.cnt DESC

The subquery results in this

+----+-----+
| id | cnt |
+----+-----+
| A  |   4 |
| B  |   2 |
| C  |   1 |
+----+-----+

Joined to your original table we have these rows:

+------+---------+-------+--------+
| u.id | u.name  | ss.id | ss.cnt |
+------+---------+-------+--------+
| A    | test a1 | A     |      4 |
| A    | test a2 | A     |      4 |
| A    | test a3 | A     |      4 |
| B    | test b1 | B     |      2 |
| B    | test b2 | B     |      2 |
| A    | test a4 | A     |      4 |
| C    | test c1 | C     |      1 |
+------+---------+-------+--------+

So we only want to return the columns from the u users table and sort by the subquery's ss.cnt. You can select u.* though it's generally better practice to be explicit in the columns you want your query to return.

SQLFiddle

You can also add sorting based on u.name if you want to sort your results within the ID groups:

ORDER BY ss.cnt DESC, u.name

SQLFiddle

Upvotes: 3

Related Questions