Obtenez l'enregistrement de valeur maximale dans chaque résultat SQL groupé

Question

Comment obtenir la ligne contenant la valeur maximale pour chaque regroupement ?

J'ai vu quelques variantes trop complexes, mais aucune n'a donné de bonne réponse. J'ai essayé l'exemple le plus simple :

Étant donné le tableau ci-dessous, qui contient des colonnes pour les personnes, les groupes et les âges, comment obtenez-vous la personne la plus âgée de chaque groupe ? (Une égalité au sein d'un groupe doit donner le premier résultat par ordre alphabétique)

Groupe de personnes Âge |
---
Bob | 1 | 32
Jill | 1 | 34
Shawn|1 |
Jacques | 2 | 29
Paul | 2 | 36
Laura| 2 | 39
Ensemble de résultats souhaité : 
Shawn 1 |
Laura | 2 | 39

            

Answer 1

La bonne solution est :

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

Comment ça marche :

Il correspondra à une ou plusieurs lignes dans o中的每一行与具有相同Group列值和较大Age列值的b中的所有行进行匹配。任何o中的行如果在Age列中没有其组中的最大值，则会与b.

LEFT JOIN使其将组中的最年长的人（包括那些独自一人的人）与来自b的一行NULLFaites un match (« Personne de plus âgé dans le groupe »).
L'utilisation de INNER JOIN empêchera ces lignes de correspondre et elles seront ignorées.

WHERE子句仅保留从b中提取的字段中具有NULLD'accord. Ce sont les plus âgés de chaque groupe.

Lectures complémentaires

Cette solution et bien d'autres sont expliquées en détail dans le livre SQL Antipatterns Volume 1 : Éviter les pièges de la programmation de bases de données.

Answer 2

Il existe un moyen très simple de le faire dans MySQL :

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

Cette méthode fonctionne car dans MySQL, vous ne pouvez pas agréger les colonnes non groupées, auquel cas MySQL ne renvoie que la première ligne. La solution consiste d’abord à trier les données dans l’ordre souhaité, puis à les regrouper selon les colonnes souhaitées.

Vous évitez le problème des sous-requêtes complexes essayant de trouver max() etc., et évitez également le problème du renvoi de plusieurs lignes lorsqu'il y a plusieurs lignes avec la même valeur maximale (d'autres réponses font cela).

Remarque : Il s'agit d'une solution uniquement pour MySQL. Toutes les autres bases de données que je connais généreront une erreur de syntaxe SQL avec le message d'erreur "Colonne non agrégée non répertoriée dans la clause group by" ou quelque chose de similaire. Étant donné que cette solution utilise un comportement non documenté, une personne plus prudente pourrait vouloir inclure un test pour s'assurer qu'elle fonctionne toujours si une future version de MySQL modifie ce comportement.

Mise à jour version 5.7 :

À partir de la version 5.7, sql-mode设置默认包含ONLY_FULL_GROUP_BY, donc pour que cela fonctionne, vous devez ne pas utiliser cette option (modifiez le fichier d'options du serveur pour supprimer ce paramètre).