ROW_NUMBER, RANK et DENSE_RANK : égalités et Top N par groupe

ROW_NUMBER attribue une position unique à chaque ligne. RANK attribue le même rang aux ex æquo et laisse des sauts. DENSE_RANK attribue le même rang sans saut. Le choix détermine le sens de « les deux meilleurs » lorsque des scores sont identiques.

Fonction	Positions pour 95, 95, 80	Usage adapté
ROW_NUMBER	1, 2, 3 avec départage explicite	Jusqu’à N lignes par groupe
RANK	1, 1, 3	Classement de compétition
DENSE_RANK	1, 1, 2	Les N meilleurs niveaux de score

PARTITION BY team recommence la numérotation pour chaque équipe. Contrairement à GROUP BY, les fonctions de fenêtre conservent les lignes individuelles. Le score et la position d’une personne peuvent apparaître dans le même résultat.

Essayez dans SQLite

Ouvrez SQL Lab, collez cette préparation et exécutez-la une fois. Lancez ensuite chaque requête séparément. Les noms des tables et des colonnes restent en anglais pour utiliser le même SQL dans toutes les langues. Le laboratoire et les leçons liées sont actuellement en anglais.

CREATE TABLE blog_scores (
  id INTEGER PRIMARY KEY, team TEXT, name TEXT, score INTEGER
);
INSERT INTO blog_scores VALUES
  (1, 'A', 'Asha', 95), (2, 'A', 'Luis', 95), (3, 'A', 'Mei', 80),
  (4, 'B', 'Noor', 90), (5, 'B', 'Omar', 70), (6, 'B', 'Pia', 70);

1. Comparer les trois fonctions avec des égalités

L’ordre de ROW_NUMBER inclut id pour départager les scores égaux de façon stable. RANK et DENSE_RANK utilisent seulement score, ce qui conserve les ex æquo. Ajouter id à leur ORDER BY de fenêtre supprimerait ces égalités. Le ORDER BY final règle l’affichage, indépendamment du calcul des fenêtres.

SELECT team, name, score,
  ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS row_num,
  RANK() OVER (PARTITION BY team ORDER BY score DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS dense_num
FROM blog_scores
ORDER BY team, score DESC, id;

Résultat attendu

team	name	score	row_num	rank_num	dense_num
A	Asha	95	1	1	1
A	Luis	95	2	1	1
A	Mei	80	3	3	2
B	Noor	90	1	1	1
B	Omar	70	2	2	2
B	Pia	70	3	2	2

2. Retourner au maximum deux personnes par équipe

Utilisez ROW_NUMBER puis filtrez la position dans une requête extérieure. L’équipe B conserve Noor et Omar ; Pia est exclue selon le départage par id. Chaque équipe retourne au plus deux lignes. SQLite ne permet pas ce résultat de fenêtre dans WHERE au même niveau, donc la CTE produit un résultat intermédiaire filtrable.

WITH ranked AS (
  SELECT team, name, score, id,
    ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS position
  FROM blog_scores
)
SELECT team, name, score
FROM ranked
WHERE position <= 2
ORDER BY team, position;

Résultat attendu

team	name	score
A	Asha	95
A	Luis	95
B	Noor	90
B	Omar	70

3. Retourner les deux meilleurs niveaux de score

DENSE_RANK conserve chaque personne appartenant aux deux meilleurs niveaux. Chaque équipe retourne trois personnes dans ces données. Ce choix convient si tous les ex æquo des niveaux admissibles doivent être inclus, mais il ne limite pas le résultat à exactement deux personnes.

WITH ranked AS (
  SELECT team, name, score, id,
    DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS score_band
  FROM blog_scores
)
SELECT team, name, score
FROM ranked
WHERE score_band <= 2
ORDER BY team, score DESC, id;

Résultat attendu

team	name	score
A	Asha	95
A	Luis	95
A	Mei	80
B	Noor	90
B	Omar	70
B	Pia	70

Exercice et vérification

Remplacez DENSE_RANK par RANK dans la dernière requête et conservez score_band <= 2. L’équipe A ne retourne plus qu’Asha et Luis : Mei a le rang 3, car les deux leaders ex æquo occupent les deux premières positions. L’équipe B retourne toujours Noor, Omar et Pia.

Avant de choisir, précisez si la demande porte sur « deux personnes », « les rangs de compétition jusqu’à deux » ou « deux scores distincts ». Ce sont trois règles métier différentes. Testez les égalités : des scores tous uniques ne révèlent pas la différence.

Pour une sélection reproductible avec ROW_NUMBER, ajoutez un critère de départage unique et stable. Sans lui, le moteur peut changer l’ordre des scores égaux. Si des scores NULL sont possibles, définissez leur admissibilité et leur ordre ou filtrez-les ; l’ordre de NULL varie selon le moteur.

Pour dédupliquer, partitionnez par la clé de doublon et triez par la date souhaitée puis un identifiant unique. Gardez la position 1 dans une requête extérieure, mais examinez d’abord les lignes écartées. Le classement permet une sélection ; il ne garantit pas qu’une suppression soit sans risque.

Documentation et suite du parcours

Poursuivez avec les leçons de classement et de clause OVER. Ces requêtes fonctionnent dans SQLite ; PostgreSQL et MySQL 8 prennent aussi en charge ces fonctions. Aucune clause propre à un moteur, comme QUALIFY, n’est supposée.

• Documentation officielle SQLite
• Documentation officielle du moteur