Leçon 12.6 · Temps de lecture : ~7 min

Le top N par groupe (en anglais « greatest N per group ») consiste à choisir les quelques meilleures lignes de chaque groupe : les trois plus gros clients de chaque magasin, les deux films les plus longs de chaque catégorie, la dernière commande de chaque client. Ce problème revient sans cesse dans les rapports et les entretiens SQL. Dans cette leçon, vous le résoudrez avec ROW_NUMBER() et PARTITION BY, verrez comment les ex æquo changent la réponse et découvrirez deux alternatives : une sous-requête corrélée et une jointure LATERAL.

Top N lignes par groupe en SQL

ORDER BY ... LIMIT 3 renvoie les trois premières lignes du résultat entier, pas de chaque groupe. Le regroupement n'aide pas non plus : GROUP BY réduit chaque groupe à une seule ligne, alors qu'il nous faut plusieurs lignes d'origine par groupe. La solution : numéroter les lignes à l'intérieur de chaque groupe et garder les N premières.

Les exemples s'exécutent sur la base d'exemple Sakila (MySQL 8.0).

L'exercice

Trouvez les trois clients qui ont le plus payé dans chacun des deux magasins.

D'abord, le total de chaque client :

SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
       SUM(p.amount) AS total_paid
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
ORDER BY total_paid DESC
LIMIT 3;

Résultat : KARL SEAL (magasin 2, 221.55), ELEANOR HUNT (magasin 1, 216.54), CLARA SHAW (magasin 1, 195.58) : les trois premiers au global, pas par magasin.

Solution 1 : ROW_NUMBER avec PARTITION BY

PARTITION BY store_id recommence la numérotation pour chaque magasin ; ORDER BY total_paid DESC donne le numéro 1 au plus gros payeur. Une fonction de fenêtrage ne peut pas être utilisée dans le WHERE de la même requête : le filtre va donc dans la requête externe :

WITH customer_total AS (
    SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
           SUM(p.amount) AS total_paid
    FROM customer c
    JOIN payment p ON p.customer_id = c.customer_id
    GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
),
ranked AS (
    SELECT store_id, first_name, last_name, total_paid,
           ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_paid DESC) AS rn
    FROM customer_total
)
SELECT store_id, first_name, last_name, total_paid, rn
FROM ranked
WHERE rn <= 3
ORDER BY store_id, rn;

Résultat : 6 lignes, trois par magasin.

Ex æquo : ROW_NUMBER, RANK ou DENSE_RANK

RHONDA KENNEDY et MARION SNYDER ont payé exactement la même somme, mais ROW_NUMBER leur a donné des numéros différents. En cas d'ex æquo, les trois fonctions répondent à des questions différentes. Regardons les films les plus longs de deux catégories, où les durées se répètent souvent :

WITH ranked AS (
    SELECT c.name AS category, f.title, f.length,
           ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS row_num,
           RANK()       OVER (PARTITION BY c.name ORDER BY f.length DESC) AS rnk,
           DENSE_RANK() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS dense_rnk
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
)
SELECT category, title, length, row_num, rnk, dense_rnk
FROM ranked
WHERE category IN ('Action', 'Comedy') AND dense_rnk <= 3
ORDER BY category, row_num;

Résultat : 9 lignes ; dans Action, deux films partagent la durée maximale de 185 minutes.

• ROW_NUMBER() <= N : exactement N lignes par groupe ; entre valeurs égales, le choix est arbitraire. Ajoutez un critère de départage dans ORDER BY, par exemple f.length DESC, f.title, pour un résultat stable.
• RANK() <= N : tous ceux qui se classeraient dans le top N, si bien qu'un groupe peut renvoyer plus de N lignes.
• DENSE_RANK() <= N : les lignes ayant les N plus grandes valeurs distinctes.

En entretien, demandez ce qui est attendu : « les 3 meilleurs films » correspond généralement à ROW_NUMBER, « les films ayant les 3 plus longues durées » à DENSE_RANK.

Solution 2 : sous-requête corrélée

Sans fonctions de fenêtrage (MySQL avant 8.0), une ligne fait partie du top N de son groupe si moins de N lignes du même groupe ont une valeur supérieure :

SELECT c.name AS category, f.title, f.length
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name = 'Action'
  AND (
      SELECT COUNT(*)
      FROM film f2
      JOIN film_category fc2 ON fc2.film_id = f2.film_id
      WHERE fc2.category_id = fc.category_id
        AND f2.length > f.length
  ) < 2
ORDER BY f.length DESC;

Résultat : 2 lignes, DARN FORRESTER et WORST BANGER, les deux films sans film plus long dans Action ; cela se comporte comme RANK() <= 2.

La sous-requête s'exécute pour chaque ligne : c'est plus lent sur de grandes tables, mais cela fonctionne dans toutes les bases.

Solution 3 : jointure LATERAL

Une sous-requête LATERAL peut faire référence aux colonnes des tables qui la précèdent dans FROM. On peut ainsi écrire littéralement « pour chaque catégorie, prendre ses 2 films les plus longs », avec ORDER BY et LIMIT à l'intérieur :

SELECT c.name AS category, top_films.title, top_films.length
FROM category c
JOIN LATERAL (
    SELECT f.title, f.length
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    WHERE fc.category_id = c.category_id
    ORDER BY f.length DESC, f.title
    LIMIT 2
) AS top_films ON TRUE
WHERE c.name IN ('Action', 'Comedy')
ORDER BY category, top_films.length DESC;

Résultat : 4 lignes : DARN FORRESTER et WORST BANGER pour Action, CONTROL ANTHEM et SATURN NAME pour Comedy.

LATERAL est pris en charge par PostgreSQL et MySQL 8.0.14+, mais pas par MariaDB ; SQL Server utilise CROSS APPLY pour la même idée. Avec un index sur les colonnes de regroupement et de tri, c'est souvent la méthode la plus rapide quand les groupes sont grands et N petit.

Pratique

• Principales zones de prise en charge par mois
• Employés les mieux payés par département
• Classement des films par popularité

Points clés :

• LIMIT et GROUP BY ne peuvent pas renvoyer plusieurs lignes par groupe ; numérotez plutôt les lignes à l'intérieur de chaque groupe.
• ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) avec WHERE rn <= N dans une requête externe est la solution standard.
• En cas d'ex æquo, ROW_NUMBER renvoie exactement N lignes, RANK peut en renvoyer plus et DENSE_RANK renvoie les N plus grandes valeurs distinctes.
• Une sous-requête corrélée résout l'exercice sans fonctions de fenêtrage, mais plus lentement.
• JOIN LATERAL (ou CROSS APPLY dans SQL Server) avec ORDER BY ... LIMIT N est une alternative lisible et souvent rapide.

Questions fréquentes

Comment sélectionner les N premières lignes de chaque groupe en SQL ?

Numérotez les lignes de chaque groupe avec ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) dans une CTE ou une sous-requête, puis gardez dans la requête externe les lignes dont le numéro est au plus N.

Pourquoi ne peut-on pas utiliser la fonction de fenêtrage dans WHERE ?

Les fonctions de fenêtrage sont calculées après WHERE, GROUP BY et HAVING, pendant le SELECT. Pour filtrer sur leur résultat, calculez-les dans une CTE ou une sous-requête et filtrez dans la requête externe. Certaines bases (Snowflake, DuckDB, BigQuery) proposent pour cela la clause QUALIFY.

Quelle est la différence entre ROW_NUMBER, RANK et DENSE_RANK pour le top N ?

ROW_NUMBER donne à chaque ligne un numéro unique : il renvoie exactement N lignes et départage les ex æquo arbitrairement. RANK donne le même rang aux valeurs égales et saute les numéros suivants : il peut renvoyer plus de N lignes. DENSE_RANK ne saute pas de numéros et renvoie toutes les lignes ayant les N plus grandes valeurs distinctes.

Comment obtenir la dernière ligne de chaque client ?

C'est un top 1 par groupe : ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) et WHERE rn = 1. Dans PostgreSQL, SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC est une alternative plus courte.