Tengo una mesa como esta

|   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 tiene la mayor cantidad de valores, luego B, etc. ¿Cómo puedo devolver filas ordenadas por número de nombres asociados con id? Intenté esto, pero como agrupé por id pierdo todos los names que están asociados con id.

SELECT id, name, COUNT(name) as name_count 
FROM users 
GROUP BY id 
ORDER BY name_count DESC
0
John 16 feb. 2018 a las 22:09

2 respuestas

La mejor respuesta

Estás cerca. Para evitar perder los nombres, debe unirse a una subconsulta para manejar el conteo:

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

La subconsulta da como resultado este

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

Unido a su tabla original tenemos estas filas:

+------+---------+-------+--------+
| 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 |
+------+---------+-------+--------+

Por lo tanto, solo queremos devolver las columnas de la tabla de usuarios u y ordenarlas por la subconsulta ss.cnt. Puede seleccionar u.*, aunque generalmente es una mejor práctica ser explícito en las columnas que desea que devuelva su consulta.

SQLFiddle

También puede agregar una clasificación basada en u.name si desea ordenar sus resultados dentro de los grupos de ID:

ORDER BY ss.cnt DESC, u.name

SQLFiddle

3
Andrew 16 feb. 2018 a las 19:36

Se puede resolver incluso sin subconsulta si hay una columna que es única en la tabla users. Por ejemplo, si name es único, será como sigue:

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
0
Radim Bača 16 feb. 2018 a las 19:38