ROW_NUMBER
Numéroter les données retournées par une requête SQL
Fonction :
La fonction ROW_NUMBER() permet de numéroter les enregistrements retournés par une requête SQL.
La numérotation peut être faite sur l’ensemble des enregistrements retournés ou sur chaque groupe d’enregistrements retournés.
Syntaxe :
La fonction ROW_NUMBER() doit être indiquée dans la clause SELECT.
Elle est suivie de la clause OVER qui permet de définir :
- L’ordre sur lequel reposera la numérotation.
Cet ordre est à définir avec une clause ORDER BY suivi de la liste des champs constituant la séquence de numérotation. - Le groupe de données pour chacun desquels la numérotation se fera, c’est à dire les groupes de données pour chacun desquels la numérotation repartira de la valeur 1.
Ces groupes de données sont à définir à l’aide la clause PARTITION BY.
ROW_NUMBER() OVER(
PARTITION BY my_field_groupe_id_1, my_field_group_id_2
ORDER BY my_field_order_1, my_field_order_2
) my_result_field_name
Exemples :
Voici la table CDM_FOOT_1 contenant la liste des pays ayant gagné la coupe du monde de football depuis 1930 :
SELECT PAYS_VAINQUEUR, ANNEE_VICTOIRE FROM CDM_FOOT_1;
Pour numéroter les enregistrements en commençant par le premier pays ayant remporté la coupe du monde, il faut utiliser la requête suivante :
SELECT PAYS_VAINQUEUR, ANNEE_VICTOIRE,
ROW_NUMBER() OVER (ORDER BY ANNEE_VICTOIRE ASC) ORDRE_VAINQUEUR
FROM CDM_FOOT_1
ORDER BY ANNEE_VICTOIRE DESC;
La colonne ORDRE_VAINQUEUR, résultat du ROW_NUMBER(), contient bien la numérotation de tous les enregistrements retournés, en partant de l’année de victoire la plus petite comme il a été indiqué dans la clause ORDER BY ANNEE_VICTOIRE ASC.
Si l’on souhaite avoir une numérotation pour chaque pays, il faut utiliser la clause PARTITION BY pour définir le groupe de données sur lequel porte la numérotation :
SELECT PAYS_VAINQUEUR, ANNEE_VICTOIRE,
ROW_NUMBER() OVER (PARTITION BY PAYS_VAINQUEUR
ORDER BY ANNEE_VICTOIRE ASC
) NUMERO_VICTOIRE
FROM CDM_FOOT_1
ORDER BY PAYS_VAINQUEUR;
La colonne résultat NUMERO_VICTOIRE contient bien le numéro de chaque victoire de chaque pays.
Ainsi il est facile de voir que la seconde victoire de la France a eu lieu en 2018 par exemple.
Les résultats d’une numérotation par ROW_NUMBER() peuvent être ensuite utilisés dans une autre requête.
Par exemple pour comptabiliser le nombre de victoire par pays, il est possible d’utiliser la requête suivante :
WITH TB_CDM AS(
SELECT PAYS_VAINQUEUR, ANNEE_VICTOIRE,
ROW_NUMBER() OVER (
PARTITION BY PAYS_VAINQUEUR
ORDER BY ANNEE_VICTOIRE ASC
) NUMERO_VICTOIRE
FROM CDM_FOOT_1
ORDER BY PAYS_VAINQUEUR
)
SELECT PAYS_VAINQUEUR, MAX(NUMERO_VICTOIRE) AS NOMBRE_VICTOIRES
FROM TB_CDM
GROUP BY PAYS_VAINQUEUR
ORDER BY NOMBRE_VICTOIRES DESC;
La fonction ROW_NUMBER() est donc très pratique et très simple à utiliser…