MySQL “Group By” e “Order By”

Eu quero ser capaz de selecionar um monte de linhas de uma tabela de e-mails e agrupá-los pelo remetente. Minha consulta é assim:

SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` GROUP BY LOWER(`fromEmail`) ORDER BY `timestamp` DESC 

A consulta quase funciona como eu quero – ela seleciona registros agrupados por e-mail. O problema é que o assunto e o registro de data e hora não correspondem ao registro mais recente de um endereço de e-mail específico.

Por exemplo, pode retornar:

 fromEmail: john@example.com, subject: hello fromEmail: mark@example.com, subject: welcome 

Quando os registros no database são:

 fromEmail: john@example.com, subject: hello fromEmail: john@example.com, subject: programming question fromEmail: mark@example.com, subject: welcome 

Se o assunto “questão de programação” é o mais recente, como posso obter o MySQL para selecionar esse registro ao agrupar os e-mails?

Uma solução simples é agrupar a consulta em uma subseleção com a instrução ORDER primeiro e aplicar o GROUP BY mais tarde :

 SELECT * FROM ( SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` ORDER BY `timestamp` DESC ) AS tmp_table GROUP BY LOWER(`fromEmail`) 

Isso é semelhante ao uso da junit, mas parece muito mais bonito.

O uso de colunas não agregadas em uma cláusula SELECT com GROUP BY não é padrão. O MySQL geralmente retorna os valores da primeira linha que encontra e descarta o resto. Qualquer cláusula ORDER BY se aplicará apenas ao valor da coluna retornada, e não aos valores descartados.

IMPORTANTE ATUALIZAÇÃO Selecionar colunas não agregadas usadas para trabalhar na prática, mas não deve ser confiável. De acordo com a documentação do MySQL “isso é útil principalmente quando todos os valores em cada coluna não agregada não nomeados no GROUP BY são os mesmos para cada grupo. O servidor é livre para escolher qualquer valor de cada grupo, portanto, a menos que sejam iguais, os valores escolhidos são indeterminados “.

A partir de 5.6.21 eu tenho notado problemas com o GROUP BY na tabela temporária revertendo a ordenação ORDER BY.

A partir de 5.7.5 ONLY_FULL_GROUP_BY é ativado por padrão, ou seja, é impossível usar colunas não agregadas.

Veja http://www.cafewebmaster.com/mysql-order-sort-group https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https: //dev.mysql .com / doc / refman / 5.7 / pt / group-by-handling.html

Aqui está uma abordagem:

 SELECT cur.textID, cur.fromEmail, cur.subject, cur.timestamp, cur.read FROM incomingEmails cur LEFT JOIN incomingEmails next on cur.fromEmail = next.fromEmail and cur.timestamp < next.timestamp WHERE next.timestamp is null and cur.toUserID = '$userID' ORDER BY LOWER(cur.fromEmail) 

Basicamente, você une a tabela em si, procurando por linhas posteriores. Na cláusula where você afirma que não pode haver linhas posteriores. Isso lhe dá apenas a última linha.

Se houver vários e-mails com o mesmo timestamp, essa consulta precisaria de refinamento. Se houver uma coluna de ID incremental na tabela de email, altere o JOIN da seguinte forma:

 LEFT JOIN incomingEmails next on cur.fromEmail = next.fromEmail and cur.id < next.id 

Faça um GROUP BY após o ORDER BY envolvendo sua consulta com o GROUP BY assim:

 SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from 

De acordo com o padrão SQL, você não pode usar colunas não agregadas na lista de seleção. O MySQL permite tal uso (sem o uso do modo ONLY_FULL_GROUP_BY), mas o resultado não é previsível.

ONLY_FULL_GROUP_BY

Você deve primeiro selecionar fromEmail, MIN (read) e, em seguida, com a segunda query (ou subconsulta) – Subject.

Como apontado em uma resposta já, a resposta atual está errada, porque o GROUP BY seleciona arbitrariamente o registro da janela.

Se alguém estiver usando o MySQL 5.6, ou o MySQL 5.7 com ONLY_FULL_GROUP_BY , a consulta correta (determinística) é:

 SELECT incomingEmails.* FROM ( SELECT fromEmail, MAX(timestamp) `timestamp` FROM incomingEmails GROUP BY fromEmail ) filtered_incomingEmails JOIN incomingEmails USING (fromEmail, timestamp) GROUP BY fromEmail, timestamp 

Para que a consulta seja executada de maneira eficiente, é necessária uma indexação adequada.

Note que para simplificar, eu removi o LOWER() , que na maioria dos casos, não será usado.

Lutei com essas duas abordagens para consultas mais complexas do que as mostradas, porque a abordagem da subconsulta era horrivelmente ineficiente, independentemente dos índices que eu colocasse, e porque não conseguia obter a auto-união externa através do Hibernate

A melhor (e mais fácil) maneira de fazer isso é agrupar por algo que é construído para conter uma concatenação dos campos que você precisa e, em seguida, retirá-los usando expressões na cláusula SELECT. Se você precisar fazer um MAX (), certifique-se de que o campo que você deseja MAX () está sempre no final mais significativo da entidade concatenada.

A chave para entender isso é que a consulta só pode fazer sentido se esses outros campos forem invariantes para qualquer entidade que satisfaça o Max (), portanto, em termos do tipo, as outras partes da concatenação podem ser ignoradas. Explica como fazer isso na parte inferior deste link. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Se você puder obter um evento insert / update (como um trigger) para pré-computar a concatenação dos campos, você pode indexá-lo e a consulta será tão rápida quanto se o grupo tivesse acabado apenas o campo que você queria MAX ( ). Você pode até usá-lo para obter o máximo de vários campos. Eu o uso para fazer consultas contra trees multidimensionais expressas como conjuntos nesteds.