Obtenir les n premiers éléments de chaque catégorie

Présentation
Supposons qu'une table ELEMENT et une table CATEGORIE sont composées comme suit :

CREATE TABLE `ma_base`.`CATEGORIE` (
`id_categorie` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`nom_categorie` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_categorie`)
)
ENGINE = InnoDB;

CREATE TABLE `ma_base`.`ELEMENT` (
`id_element` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`nom_element` VARCHAR(45) NOT NULL,
`id_categorie` INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (`id_element`),
CONSTRAINT `FK_ELT_CAT` FOREIGN KEY `FK_ELT_CAT` (`id_categorie`)
REFERENCES `categorie` (`id_categorie`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE = InnoDB;


Le but de la requête est de ramener les n premiers éléments de chaque catégorie par ordre d'identifiant. Pour cela, on fait ce qu'on appelle une division relationnelle, comme suit :

SELECT
e.id_element, e.nom_element, c.id_categorie, c.nom_categorie
FROM
ELEMENT e
INNER JOIN
CATEGORIE c ON e.id_categorie = c.id_categorie
WHERE (
SELECT COUNT(*)
FROM ELEMENT e1
WHERE e1.id_categorie = e.id_categorie
AND e1.id_element < e.id_element
) < n


Il suffit de remplacer n par la valeur souhaitée.

Pour obtenir les n derniers éléments de chaque catégorie, il suffit d'inverser le sens de l'inégalité dans la sous-requête :

SELECT
e.id_element, e.nom_element, c.id_categorie, c.nom_categorie
FROM
ELEMENT e
INNER JOIN
CATEGORIE c ON e.id_categorie = c.id_categorie
WHERE (
SELECT COUNT(*)
FROM ELEMENT e1
WHERE e1.id_categorie = e.id_categorie
AND e1.id_element > e.id_element
) < n

Attention, cette requête ne fonctionne qu'avec une version de MySQL supportant les sous-requêtes (version 4.1 ou postérieure).
Détails
Avatar de Cédric Duprez
Rédacteur / Modérateur
Voir tous les téléchargements de l'auteur
Licence : Autre
Date de mise en ligne : 13 février 2011




Avatar de gene69 gene69 - Membre émérite https://www.developpez.com
le 14/02/2011 à 10:49
ça à l'air sympa. ya l'équivalent d'une base scott/tiger pour mysql? je veux dire la même chose avec des données.

point de vue calculs, c'est gourmand?
Avatar de pacmann pacmann - Membre émérite https://www.developpez.com
le 08/12/2011 à 14:09
Salut !

Je trouve ça juste un peu dommage que l'exemple donne un tri sur une PK technique

Genre j'aurais bien vu par exemple une date de création à la place, et combiner l'ordre date_creation + increment (pour départager les ex-aequos et garantir qu'on en sorte au plus n)
Avatar de CinePhil CinePhil - Modérateur https://www.developpez.com
le 08/12/2011 à 14:24
Je me suis posé la même question tout récemment car j'ai eu à sortir un top 5 selon deux critères, l'un classé en ASC et l'autre en DESC.

Comme il n'y a pas beaucoup de catégories, je suis passé par des requêtes UNION et LIMIT 5. Un truc du genre :
Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT tmp.les_colonnes 
FROM 
( 
	( 
		SELECT la_categorie, les_colonnes 
		FROM la_table 
		WHERE la_categorie = 1 
		ORDER BY un_critere ASC, un_autre_critere DESC 
		LIMIT 5 
	) 
	UNION ALL 
	( 
		SELECT la_categorie, les_colonnes 
		FROM la_table 
		WHERE la_categorie = 2 
		ORDER BY un_critere ASC, un_autre_critere DESC 
		LIMIT 5 
	) 
	UNION ALL 
-- etc 
) tmp 
ORDER BY la_categorie, un_critere ASC, un_autre_critere DESC
Avatar de pacmann pacmann - Membre émérite https://www.developpez.com
le 08/12/2011 à 14:29
Quand tu as deux (ou plus) critères, il faut réaliser les combinaisons en priorisant.

Dans ton cas, la condition du count(*) de ced serait un truc du genre :
Code : Sélectionner tout
1
2
 
WHERE a.un_critere < b.un_critèreASC OR (a.un_critere = b.un_critere AND a.un_autre_critere > b.un_autre_critere)
Avatar de Sergejack Sergejack - Membre éprouvé https://www.developpez.com
le 16/12/2011 à 14:10
Ça ressemble (en même temps il n'y a pas des milliers de possibilité en MySQL) à un query que j'ai écrit récemment.
Il y a une subtilité supplémentaire dans mon query, c'est une optimisation prenant tout son sens lorsque des critères de recherche impliquent des jointures.

En effet, si des lignes doivent être éliminées du résultats, elles doivent l'être autant dans le sous query qui calcul l'ordre de la ligne (dépendante d'un tri) que dans le query principal.
Pour ne pas répéter deux fois des jointures qui ne serviraient qu'au filtrage, je procède donc ainsi :

Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
SELECT 
	H.id 
	, H.stockID 
	, H.value 
	, H.date 
	, H.userID 
	, U.name AS userName 
FROM #__mystock_history AS H 
LEFT JOIN #__users AS U ON (U.id = H.userID) 
WHERE H.stockID IN (%1$s) 
AND ( 
	SELECT 
		 COUNT(*) * SUM(CASE WHEN H.id = H2.id THEN 1 ELSE 0 END) 
	FROM #__mystock_history AS H2 
	LEFT JOIN #__mystock_cancel AS C1 ON (C1.canceledID = H2.id) 
	LEFT JOIN #__mystock_cancel AS C2 ON (C2.correctionID = H2.id) 
	WHERE H2.stockID = H.stockID 
	AND C1.canceledID IS NULL 
	AND C2.correctionID IS NULL 
	AND H2.Date >= H.Date 
	AND ( 
		H2.Date > H.Date 
		OR 
		H2.id >= H.id 
	) 
) BETWEEN 1 AND %2$d 
ORDER BY H.stockID ASC, H.date DESC
%1$s = une liste d'identifiant séparés par des virgules (ex "1,2,3,4")
%2$d = Le nombre maximale de lignes à obtenir par "groupe"

L'idée c'est d'obtenir dans le sous query une formule qui puisse m'inidquer deux choses :
1) l'ordre de la ligne
2) si la ligne convient au filtre

Le sous query renverra 0 si la ligne ne convient pas au filtre et l'ordre de la ligne sinon.
Avatar de SQLpro SQLpro - Rédacteur https://www.developpez.com
le 21/03/2013 à 15:21
Elle n'est valable QUE dans le cas ou vous utilisez une clef primaire comme comparaison d’inégalité. Si vous utilisez une colonne quelconque, cela peut conduire à des résultats faux.

Dans ce ac, seule la fonction ROW_NUMBER() ou RANK(), implémentée dans tous les SGBDR sauf MySQL sera la seule solution.

A +
Avatar de Sergejack Sergejack - Membre éprouvé https://www.developpez.com
le 21/03/2013 à 17:08
En MySQL, vous pouvez définir une variable entière à 0 et renvoyer sa valeur tout en l'incrémentant dans un select ordonné (order by...).
Je ne me souviens pas de la syntaxe exacte, mais c'est une chose à connaître dans ces cas-là.

Note : Quel déterrement !
Avatar de alassanediakite alassanediakite - Membre expert https://www.developpez.com
le 18/05/2013 à 15:21
Salut
Un exemple avec variable initialisée à zéro
Code : Sélectionner tout
1
2
SET @rank=0; 
SELECT @rank:=@rank+1 as rank, ID, Name FROM city;
@+
Developpez.com décline toute responsabilité quant à l'utilisation des différents éléments téléchargés.
Contacter le responsable de la rubrique MySQL