ROW_NUMBER, RANK, DENSE_RANK
Les fonctions de classement numérotent les lignes selon un tri, éventuellement par groupe. Elles répondent aux questions de type « top N par catégorie » qu'un simple LIMIT ne sait pas traiter.
- classement
- rang
- top N par groupe
- ROW_NUMBER
- RANK
- DENSE_RANK
- NTILE
Syntaxe
ROW_NUMBER() OVER (PARTITION BY groupe ORDER BY critere DESC)RANK() OVER (PARTITION BY groupe ORDER BY critere DESC)DENSE_RANK() OVER (PARTITION BY groupe ORDER BY critere DESC)Exemple
WITH classement AS ( SELECT d.name AS service, e.name, e.salary, ROW_NUMBER() OVER ( PARTITION BY e.department_id ORDER BY e.salary DESC ) AS rang FROM employees e JOIN departments d ON d.id = e.department_id)SELECT service, name, salary, rangFROM classementWHERE rang <= 2ORDER BY service, rang;-- Jeu de données : entreprise-- Les deux meilleurs salaires de chaque service-- Résultat attendu :-- Direction | Isabelle Renard | 95000 | 1-- Ingénierie | Olivier Caron | 78000 | 1-- Ingénierie | Quentin Faure | 54000 | 2-- Marketing | Patricia Meyer | 62000 | 1-- Marketing | Ursula Klein | 45000 | 2-- Ressources Humaines | Victor Nguyen | 48000 | 1-- Ventes | Sandrine Lopez | 58000 | 1-- Ventes | Théo Brun | 42000 | 2À retenir
- Différence sur des valeurs 100, 90, 90, 80 : ROW_NUMBER donne 1, 2, 3, 4, RANK donne 1, 2, 2, 4 (il saute un rang après l'égalité), DENSE_RANK donne 1, 2, 2, 3.
- Choix : ROW_NUMBER pour exactement N lignes par groupe ou pour dédoublonner, RANK pour un classement sportif, DENSE_RANK pour « les 3 meilleurs montants distincts ».
- Piège : avec des ex æquo, ROW_NUMBER les départage au hasard, et le résultat peut changer d'une exécution à l'autre. Ajoutez un critère de départage : ORDER BY salary DESC, id.
- Piège : WHERE rang <= 2 doit se trouver dans la requête externe, comme ici. QUALIFY, qui filtre directement, existe dans DuckDB, Snowflake ou BigQuery, mais pas dans SQLite, PostgreSQL ni SQL Server.
- NTILE(4) OVER (ORDER BY salary) répartit les lignes en quatre quartiles numérotés de 1 à 4.
Voir aussi
- Fonctions de fenêtre (OVER)Une fonction de fenêtre calcule un agrégat sur un groupe de lignes tout en gardant chaque ligne, à l'inverse de GROUP BY. On compare ainsi chaque salaire à la moyenne de son service.
- LAG, LEAD et cumulsLAG lit la valeur de la ligne précédente, LEAD celle de la suivante, et SUM() OVER (ORDER BY …) calcule un cumul. On compare ainsi une période à la précédente ou on suit un total qui grandit.
- LIMIT / OFFSETLIMIT garde les n premières lignes du résultat trié, OFFSET en saute m avant de commencer. C'est l'outil des classements (top 3) et de la pagination d'une liste dans une application.