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

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).

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

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)

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
2
SET @rank=0;
SELECT @rank:=@rank+1 as rank, ID, Name FROM city;