1

Pour sélectionner les N plus grand éléments par type dans une table en 5.7, utilisez une requête imbriquée et un compteur par le biais d’une variable. En 8, utilisez les Window Functions.

Sélectionner les N premiers éléments de chaque type

Problématique

Il arrive souvent, en MySQL, d’avoir besoin de sélectionner les éléments les plus *quelque chose* d’une liste (les 5 produits les plus chers par catégorie, les 10 meilleurs joueurs pour chacun des classements, etc). Pour cela, on peut soit utiliser plusieurs tables (c’est parfaitement adapté aux “10 meilleurs joueurs de chaque classement” puisqu’on n’ajoute pas de classement à la volée tous les jours) soit utiliser une seule table avec une colonne indiquant le type d’objet de la ligne (par exemple, dans une table “fruits”, une colonne indiquant “poire”, “pomme”, “orange”, etc).

Dans le cas de plusieurs tables, sélectionner les N premiers joueurs (ou les N lignes les plus *quelque chose*) est aisée, car il suffit de faire un …ORDER BY … LIMIT … sur chacune des tables.

Une table, K type d’item

En revanche, dans le cas d’une seule table, cette sélection est plus difficile. Impossible de faire un ORDER BY … LIMIT car cela sélectionnerait les N premiers éléments de la liste entière, et non les N premiers éléments de chacun des types.

Dans le fichier d’exemple suivant, je vous propose donc une méthode pour sélectionner l’élément (item) le plus cher (price) pour chaque type d’item. Mais cette méthode a une limite majeure: s’il existe deux éléments qui sont les plus cher pour un même type (par exemple, 2 item de type 4 valant chacune 10), alors la requête renverra deux lignes. Utiliser un GROUP BY ne change pas grand chose, car cela n’assure pas que les données récupérées soient cohérentes.

La bonne solution

La bonne alternative est d’utiliser une variable comme compteur: on classe les lignes de la table item de la plus chère (price) à la moins chère, et on donne un numéro à chacune de ces lignes; numéro que l’on remet à zéro lorsqu’on passe au type suivant. Une fois cela fait, il ne reste plus qu’à sélectionner les lignes de numéro inférieur à N pour chaque type d’item.

Cette méthode présente un inconvénient: MySQL va cloner la table item lors de cette opération (qui implique de faire deux requêtes imbriquées). Ce clone va donc prendre du temps si la table est lourde, principalement si elle contient du texte. Vous pouvez l’éviter en ne copiant que les identifiants (id) des lignes de item, pour faire ensuite une jointure quand vient le temps de récupérer les données. Je vous recommande de tester les deux, et de sélectionner la méthode la plus véloce dans votre cas d’utilisation (en considérant les caches MySQL). Au final, cela donne:

SELECT type, id, name, price, row_number FROM (
	SELECT
		*,
	   @num := if(@type = type, @num + 1, 1) as row_number,
	   @type := type as dummy
	FROM items
	ORDER by type, price DESC
) AS t
WHERE t.row_number < = 5
ORDER BY type ASC, price DESC;
SELECT i.* FROM (
	SELECT
		id,
		@num := if(@type = type, @num + 1, 1) as row_number,
		@type := type as dummy
	FROM items ORDER by type, price DESC
) AS t
INNER JOIN items AS i ON i.id = t.id
WHERE t.row_number < = 5;
Sélectionner les 5 produits les plus chers de chaque type

MySQL 8: Window Function

Notez qu'avec MySQL 8, vous pouvez également utiliser les Window Function, qui vous permettront d'obtenir le même résultat de manière plus synthétique et fiable.

SELECT ROW_NUMBER() OVER (PARTITION BY type ORDER BY price DESC) AS rk, * FROM items ORDER BY type, price DESC HAVING rk <= 5
Window Function en MySQL 8

Je ne maîtrise pas encore parfaitement ces Window Functions, donc il existe peut-être mieux qu'un HAVING, mais le concept est là.

1