MySQL – ÚNASE, AGRUPE POR, ORDEN POR

Lo sé, es un tema común.
Esta pregunta aborda un caso específico sin embargo, desnudo conmigo, por favor.


Entonces, el problema que tuve primero con la siguiente consulta fue que la cláusula group by se realizó antes del order by :

La columna saved.recipe_id es un integer generado por UNIX_TIMESTAMP()

 SELECT saved.recipe_id, saved.`date`, user.user_id FROM saved JOIN user ON user.id = saved.user_id GROUP BY saved.recipe_id ORDER BY saved.`date` DESC 

Así que probé todo tipo de soluciones posibles con subconsultas y otras bs. Al final terminé probando algunas subconsultas diferentes en la cláusula join que me requería cambiar el order de la tabla de la cláusula from a la cláusula join . Decidí probar lo siguiente:

 SELECT saved.recipe_id, saved.`date`, user.user_id FROM user JOIN saved ON user.id = saved.user_id GROUP BY saved.recipe_id ORDER BY saved.`date` DESC 

Por alguna razón, esto parece orderar correctamente, pero ¿por qué ?
¿Cómo puede este cambio hacer que mi consulta se ordere más correctamente que antes?
¿Realmente? ¿o simplemente lo hace para los casos de testing contra los que lo puse?

Entonces, el problema que tuve primero con la siguiente consulta fue que la cláusula group by se realizó antes del pedido por:

Esto no es un problema. Así es como se define SQL y cómo funciona. El group by crea un nuevo set de filas y order by orderando esas filas.

No hay problema para orderar aquí. Existe un problema de "comprensión de SQL". Su order by solo ordera los resultados de la consulta. Estos resultados son producidos por el group by , y el order de las uniones no tiene nada que ver con los resultados.

Está utilizando una extensión MySQL llamada Columnas ocultas. Esto es cuando tiene una consulta de agregación que tiene columnas en las cláusulas select (o having or order by ) que no son parte de las funciones de agregación ( sum() , etc) o parte del group by . Aquí hay una cita de la documentation :

MySQL amplía el uso de GROUP BY para que la list de selección pueda referirse a columnas no agregadas no nombradas en la cláusula GROUP BY. Esto significa que la consulta anterior es legal en MySQL. Puede usar esta function para get un mejor performance al evitar la sorting y sorting de columnas innecesarias. Sin embargo, esto es útil principalmente cuando todos los valores en cada columna no agregada no nombrada en GROUP BY son los mismos para cada grupo. El server puede elegir libremente cualquier valor de cada grupo, de modo que, a less que sean iguales, los valores elegidos son indeterminados. Además, la selección de valores de cada grupo no puede verse influenciada por la adición de una cláusula ORDER BY. La sorting del set de resultados ocurre después de que se han elegido los valores, y ORDER BY no afecta los valores dentro de cada grupo que el server elige.

Presumiblemente, desea la date más reciente y el usuario asociado con eso. La siguiente consulta hace lo que quiere de forma correcta y consistente:

 SELECT saved.recipe_id, max(saved.`date`) as MostRecentDate, substring_index(group_concat(user.user_id), ',', 1) as MostRecentUser FROM user JOIN saved ON user.id = saved.user_id GROUP BY saved.recipe_id ORDER BY max(saved.`date`) DESC; 

Por lo que recuerdo, GROUP BY siempre se realiza antes de ORDER BY. Si recupera cualquier columna que no esté en ninguna function de agregación, el resultado para esa columna será aleatorio. El order correcto de la segunda consulta es accidental.

En cambio, saved.date use MAX(saved.date)

Entonces obtendrás un order determinado de cada grupo y luego orderarás los resultados determinados.