La meilleure note de chaque matière, et la colonne « nue »

SELECT matiere, MAX(valeur), eleve FROM Note GROUP BY matiere;

Elle donne le bon résultat sur SQLite, et c'est un accident. Mesuré :

Les trois lignes sont justes. Mais la colonne eleve est une colonne nue : elle ne figure ni dans le GROUP BY, ni dans une fonction d'agrégation. Or un paquet contient plusieurs valeurs de eleve — laquelle le moteur doit-il rendre ? La norme SQL99 ne le définit pas, et rejette la requête.

Ce que fait SQLite est documenté : quand la requête contient un unique MIN ou MAX, il rend les colonnes nues de la ligne où l'extremum est atteint. Vérifié en remplaçant MAX par MIN : la colonne eleve change et suit le minimum ($2$, $1$, $2$).

Et voici pourquoi il ne faut pas s'y fier : remplaçons l'agrégat par COUNT.

SELECT matiere, COUNT(*), eleve FROM Note GROUP BY matiere;
-- 10 | 2 | 1     11 | 2 | 1     12 | 1 | 2

La garantie disparaît : eleve vaut maintenant la première ligne rencontrée du paquet, ce qui n'a aucun sens et ne dépend d'aucune règle stable. Le même motif d'écriture donne un résultat juste dans un cas et arbitraire dans l'autre, sans que rien ne distingue les deux à l'œil.

Les deux formes portables, toutes deux mesurées et rendant les trois bonnes lignes :

-- (a) sous-requête corrélée : « la note égale au maximum de sa matière »
SELECT n.matiere, n.eleve, n.valeur FROM Note n
WHERE n.valeur = (SELECT MAX(n2.valeur) FROM Note n2 WHERE n2.matiere = n.matiere);

-- (b) jointure sur l'agrégat : on calcule les maxima, puis on les rejoint
SELECT n.matiere, n.eleve, n.valeur FROM Note n
JOIN (SELECT matiere, MAX(valeur) AS m FROM Note GROUP BY matiere) t
  ON t.matiere = n.matiere AND t.m = n.valeur;

Elles répondent en outre à une question que la première ne posait pas : s'il y a deux élèves à la meilleure note, ces deux formes rendent deux lignes — ce qui est la bonne réponse — là où la version à colonne nue en aurait rendu une seule, arbitrairement choisie. Une requête qui ne peut rendre qu'une ligne par paquet ne peut pas répondre à « qui », quand ils sont plusieurs.