Eu tenho uma mesa que se parece com este chamador 'makerar'
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
E eu quero seleccionar a avg máxima para cada nome.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
mas eu vou ter um erro,
ERROR: column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
então eu faço isto
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
No entanto, isto não dará os resultados pretendidos, e a saída incorreta abaixo é mostrada.
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Os Resultados Reais devem ser
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Como posso resolver este problema?
Nota: Esta tabela é uma VIEW criada a partir de uma operação anterior.
Sim, este é um problema comum de agregação. Antes de SQL3 (1999), os campos selecionados devem aparecer na cláusula GROUP BY
[*].
Para resolver esta questão, você deve calcular o agregado em uma subconsulta e então juntar-se a ela para obter as colunas adicionais que você'd precisa mostrar:
SELECT m.cname, m.wmname, t.mx
FROM (
SELECT cname, MAX(avg) AS mx
FROM makerar
GROUP BY cname
) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Mas você também pode usar funções de janela, que parecem mais simples:
SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;
A única coisa com este método é que ele mostrará todos os registros (as funções de janela não agrupam). Mas ele mostrará o correto (ou seja, máximo no nível cname
) MAX
para o país em cada linha, então ele's depende de você:
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
A solução, indiscutivelmente menos elegante, para mostrar o único (cname, wmname)
tuples que corresponde ao valor máximo, é:
SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
m.cname, m.wmname, t.avg AS mx
FROM (
SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn
FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
[*]: Curiosamente, mesmo que a especificação permita seleccionar campos não agrupados, os motores principais parecem não gostar muito. Oracle e SQLServer apenas don't permitem isso de forma alguma. Mysql costumava permitir isso por padrão, mas agora desde 5.7 o administrador precisa habilitar essa opção (ONLY_FULL_GROUP_BY
) manualmente na configuração do servidor para que esse recurso seja suportado...
Em Postgres, você também pode utilizar a sintaxe especial DISTINCT ON (expressão)
:
SELECT DISTINCT ON (cname)
cname, wmname, avg
FROM
makerar
ORDER BY
cname, avg DESC ;
SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
SELECT cname, MAX(avg) max
FROM makerar
GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;
Utilizando rank()
window function:
SELECT cname, wmname, avg
FROM (
SELECT cname, wmname, avg, rank()
OVER (PARTITION BY cname ORDER BY avg DESC)
FROM makerar) t
WHERE rank = 1;
**Nota***
Qualquer um deles irá preservar múltiplos valores máximos por grupo. Se você quiser apenas um registro por grupo, mesmo que haja mais de um registro com avg igual a max você deve verificar @ypercube's resposta.