example - MySQL "Agrupar por" y "Ordenar por"

order by group by sql server

Question

Quiero poder seleccionar un grupo de filas de una tabla de correos electrónicos y agruparlos por el remitente. Mi consulta se ve así:

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

La consulta casi funciona como quiero: selecciona registros agrupados por correo electrónico. El problema es que el tema y la marca de tiempo no corresponden al registro más reciente de una dirección de correo electrónico en particular.

Por ejemplo, podría regresar:

fromEmail: [email protected], subject: hello
fromEmail: [email protected], subject: welcome

Cuando los registros en la base de datos son:

fromEmail: [email protected], subject: hello
fromEmail: [email protected], subject: programming question
fromEmail: [email protected], subject: welcome

Si el tema "pregunta de programación" es el más reciente, ¿cómo puedo obtener MySQL para seleccionar ese registro al agrupar los correos electrónicos?

Answer 1

Aquí hay un enfoque:

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)

Básicamente, te unes a la tabla en sí mismo, buscando filas posteriores. En la cláusula where indica que no puede haber filas posteriores. Esto le da solo la última fila.

Si puede haber varios correos electrónicos con la misma marca de tiempo, esta consulta necesitaría refinación. Si hay una columna de ID incremental en la tabla de correo electrónico, cambie JOIN como:

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

Answer 2

Como ya se señaló en una respuesta, la respuesta actual es incorrecta, porque GROUP BY selecciona arbitrariamente el registro de la ventana.

Si uno está utilizando MySQL 5.6, o MySQL 5.7 con ONLY_FULL_GROUP_BY , la consulta correcta (determinista) es:

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 la consulta se ejecute de manera eficiente, se requiere una indexación adecuada.

Tenga en cuenta que, para simplificar, LOWER() el LOWER() , que en la mayoría de los casos, no se usará.

Answer 3

De acuerdo con el estándar SQL, no puede usar columnas no agregadas en la lista de selección. MySQL permite dicho uso (sin usar el modo ONLY_FULL_GROUP_BY) pero el resultado no es predecible.

ONLY_FULL_GROUP_BY

Primero debe seleccionar entre Correo electrónico, MIN (leer) y luego, con segunda consulta (o subconsulta): Asunto.

Answer 4

Haz un GROUP BY después de ORDER BY al envolver tu consulta con el GROUP BY así:

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

Answer 5

Luché con estos dos enfoques para consultas más complejas que las que se muestran, porque el enfoque de la subconsulta era terriblemente ineficiente sin importar los índices que ponga, y porque no pude obtener la auto-unión externa a través de Hibernate.

La mejor (y más fácil) forma de hacerlo es agrupar por algo que está construido para contener una concatenación de los campos que necesita y luego sacarlos usando expresiones en la cláusula SELECT. Si necesita hacer un MAX (), asegúrese de que el campo sobre el que desea MAX () esté siempre en el extremo más significativo de la entidad concatenada.

La clave para entender esto es que la consulta solo puede tener sentido si estos otros campos son invariables para cualquier entidad que satisfaga Max (), por lo que en términos del orden en que se pueden ignorar las otras piezas de la concatenación. Explica cómo hacer esto en la parte inferior de este enlace. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Si puede obtener un evento insertar / actualizar (como un disparador) para calcular previamente la concatenación de los campos, puede indexarlo y la consulta será tan rápida como si el grupo estuviera sobre el campo que realmente quería MAX ( ) Incluso puede usarlo para obtener el máximo de múltiples campos. Lo uso para hacer consultas contra árboles multidimensionales expresados ​​como conjuntos anidados.

Answer 6

Una solución simple es envolver la consulta en una subselección con la instrucción ORDER primero y aplicar GROUP BY después :

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

Esto es similar a usar la unión, pero se ve mucho mejor.

El uso de columnas no agregadas en un SELECT con una cláusula GROUP BY no es estándar. MySQL generalmente devolverá los valores de la primera fila que encuentre y descartará el resto. Cualquier cláusula ORDER BY solo se aplicará al valor devuelto de la columna, no a los descartados.

ACTUALIZACIÓN IMPORTANTE Seleccionar columnas no agregadas usadas para trabajar en la práctica pero no se debe confiar en ellas. Según la documentación de MySQL "esto es útil principalmente cuando todos los valores en cada columna no agregada no nombrada en el GRUPO BY son iguales para cada grupo. El servidor puede elegir libremente cualquier valor de cada grupo, por lo tanto, a menos que sean iguales, los valores elegidos son indeterminados ".

A partir del 5.6.21, he notado problemas con GROUP BY en la tabla temporal que revierte la ordenación ORDER BY.

A partir de 5.7.5 ONLY_FULL_GROUP_BY está habilitado por defecto, es decir, es imposible usar columnas no agregadas.

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