Registro com resultado de GROUP BY
Em diversos casos utilizamos GROUP BY com uma função de agregação, como MAX(), para calcular o valor dentro de cada grupo. Mas como fazer para recuperar toda linha, e não só a coluna calculada?
Considerando o esquema e os dados como demonstrados na imagem abaixo, descobrir a maior quantidade de vezes que uma música de cada banda foi executada é bastante simples:
SELECT id_artista, MAX(plays) FROM musica GROUP BY id_artista+------------+------------+
| id_artista | MAX(plays) |
+------------+------------+
| 4 | 28 |
| 8 | 9 |
| 15 | 4 |
| 16 | 6 |
+------------+------------+O problema fica um pouco mais curioso quando queremos todo o registro de cada música, e não apenas o valor calculado de determinada coluna.
Intuitivamente, talvez alguém sugira incluir os outros campos na query, mas os resultados estarão incorretos:
SELECT id_artista, MAX(plays), nome FROM musica GROUP BY id_artista+------------+------------+--------------------------+
| id_artista | MAX(plays) | nome |
+------------+------------+--------------------------+
| 4 | 28 | Martyr of the Free World |
| 8 | 9 | Lord of the Flies |
| 15 | 4 | Air Batucada |
| 16 | 6 | Sunny Side Up |
+------------+------------+--------------------------+Observe que os valores não batem. Não é Martyr of the Free World que tem 28 execuções; nem Lord of the Flies que tem 9 execuções, e assim por diante. Os valores são imprevisíveis e não representam o registro com o valor mostrado na coluna do MAX(plays).
A solução pode ser dada de algumas formas diferentes no MySQL.
Subquery dependente
Uma solução simples é filtrar as músicas onde a coluna plays tenha o valor máximo de plays daquele artista, do método mais tradicional possível:
SELECT id_artista, nome, plays
FROM musica AS m1
WHERE plays = ( SELECT MAX(plays)
FROM musica AS m2
WHERE m1.id_artista = m2.id_artista )E o resultado é obtido como esperado:
+------------+----------------+-------+
| id_artista | nome | plays |
+------------+----------------+-------+
| 4 | Unleashed | 28 |
| 8 | Running Free | 9 |
| 15 | Focus on Sight | 4 |
| 16 | The World | 6 |
+------------+----------------+-------+Todavia, com um número grande de artistas a consulta ficará ineficiente, já que a subquery é processada para cada artista, retornando o MAX(plays) correspondente que então é utilizado para filtrar as músicas com este número de execuções.
Tabela derivada
Para não ter que calcular a subquery para cada artista, podemos calcular todos os MAX(plays) em uma query na cláusula FROM e fazer um relacionamento com o resultado, como se fosse uma tabela normal:
SELECT m1.id_artista, m1.nome, m1.plays
FROM musica AS m1
JOIN (
SELECT id_artista, MAX(plays) AS plays
FROM musica
GROUP BY id_artista ) AS derivada
ON m1.id_artista = derivada.id_artista
AND m1.plays = derivada.playsO resultado é o mesmo, mas neste caso a consulta é mais eficiente, pois o MySQL calcula todos os MAX(plays) em memória uma única vez e depois apenas relaciona os registros.
Otimizando no caso de MIN() e MAX()
O próprio manual do MySQL dá uma outra dica interessante quando o objetivo é pegar todo o registro com maior valor em determinada coluna.
A técnica utiliza LEFT JOIN na mesma tabela com critérios de ser o mesmo artista e ter o número de execuções maior do que o registro da tabela original.
Ao final, a consulta retorna apenas os registros que não foram relacionados (valor NULL no LEFT JOIN), que são os registros sem nenhum outro com número de execuções maior – ou seja, os que a coluna plays é na verdade MAX(plays) daquele artista.
SELECT m1.id_artista, m1.nome, m1.plays
FROM musica m1
LEFT JOIN musica m2
ON m1.id_artista = m2.id_artista
AND m1.plays < m2.plays
WHERE m2.id IS NULLEsta fica sendo então a forma mais eficiente de recuperar o registro com maior valor em determinada coluna, já que não é necessário nenhum tipo de subquery e nem mesmo GROUP BY. E se quiser o menor valor, basta trocar o sinal.
A dica não vale, entretanto, para outras funções de agregação, como AVG() para média. Neste caso não tem jeito senão com subquery.
