ROW_NUMBER Oracle : fonction analytique SQL expliquée

ROW_NUMBER en Oracle : maîtriser la numérotation de lignes SQL

La fonction analytique ROW_NUMBER en Oracle est l’une des plus utilisées dans le traitement de données en entreprise. Elle permet d’attribuer un numéro séquentiel unique à chaque ligne d’un ensemble de résultats, selon un ordre défini. Grâce à ROW_NUMBER, il devient simple de paginer des résultats, d’éliminer des doublons ou d’extraire le enregistrement le plus récent par groupe.

Définition et utilisation de ROW_NUMBER

ROW_NUMBER() est une fonction analytique Oracle (aussi appelée fonction de fenêtrage) qui numérote les lignes de 1 à N dans une partition de données, selon un critère de tri précis. Contrairement à RANK() ou DENSE_RANK(), elle n’attribue jamais le même numéro à deux lignes, même si celles-ci ont des valeurs identiques.

Cas d’usage courants en entreprise

Pagination de résultats : afficher la page N d’un rapport avec un nombre fixe de lignes par page.
Dédoublonnage : conserver uniquement la ligne la plus récente pour chaque client ou produit.
Top N par groupe : extraire les 3 meilleures ventes par région ou par commercial.
Audit et traçabilité : numéroter les événements chronologiques par utilisateur ou par session.

Elle est disponible depuis Oracle 8i et fait partie du standard SQL:2003. Elle ne modifie pas les données source et s’utilise exclusivement dans la clause SELECT.

Syntaxe de ROW_NUMBER sous Oracle

ROW_NUMBER() OVER (
  [PARTITION BY colonne1, colonne2, ...]
   ORDER BY colonne3 [ASC | DESC]
)

Explication des paramètres

Paramètre	Obligatoire	Description
`PARTITION BY`	Non	Divise le jeu de résultats en groupes indépendants. La numérotation repart à 1 pour chaque groupe.
`ORDER BY`	Oui	Définit l’ordre dans lequel les numéros sont attribués au sein de chaque partition.
`ASC / DESC`	Non	Sens du tri. Par défaut : `ASC` (croissant).

⚠️ Le ORDER BY à l’intérieur du OVER() est indépendant du ORDER BY final de la requête. L’un trie la fenêtre analytique, l’autre trie l’affichage final.

Exemples pratiques de ROW_NUMBER en Oracle

Exemple 1 – Numéroter les commandes par client (Top 1 par groupe)

Contexte métier : une entreprise e-commerce souhaite identifier la dernière commande passée par chacun de ses clients, afin d’alimenter un tableau de bord de fidélisation.

-- Table : COMMANDES (ID_COMMANDE, ID_CLIENT, DATE_COMMANDE, MONTANT)
-- Objectif : récupérer la commande la plus récente par client

SELECT *
FROM (
    SELECT
        ID_COMMANDE,
        ID_CLIENT,
        DATE_COMMANDE,
        MONTANT,
        ROW_NUMBER() OVER (
            PARTITION BY ID_CLIENT          -- une fenêtre par client
            ORDER BY DATE_COMMANDE DESC     -- la plus récente en premier
        ) AS RN
    FROM COMMANDES
)
WHERE RN = 1;  -- on ne garde que la 1ère ligne de chaque partition

Résultat attendu : une seule ligne par client, correspondant à sa commande la plus récente. La sous-requête est nécessaire car les fonctions analytiques ne peuvent pas être filtrées directement dans un WHERE.

Exemple 2 – Pagination de résultats avec ROW_NUMBER

Contexte métier : une application RH affiche une liste de 10 employés par page. L’utilisateur demande la page 3 (lignes 21 à 30), triés par salaire décroissant.

-- Table : EMPLOYES (ID_EMPLOYE, NOM, PRENOM, DEPARTEMENT, SALAIRE)
-- Objectif : simuler une pagination (page 3, 10 lignes par page)

SELECT ID_EMPLOYE, NOM, PRENOM, DEPARTEMENT, SALAIRE
FROM (
    SELECT
        ID_EMPLOYE,
        NOM,
        PRENOM,
        DEPARTEMENT,
        SALAIRE,
        ROW_NUMBER() OVER (
            ORDER BY SALAIRE DESC   -- tri global par salaire décroissant
        ) AS RN
    FROM EMPLOYES
)
WHERE RN BETWEEN 21 AND 30;  -- lignes 21 à 30 = page 3

Astuce Oracle : Avant la version 12c, cette approche avec ROW_NUMBER() était la méthode recommandée pour paginer. Depuis Oracle 12c, la clause FETCH FIRST N ROWS ONLY est disponible, mais ROW_NUMBER() reste indispensable pour les paginations avec offset complexe.

Erreurs courantes avec ROW_NUMBER Oracle

Erreur : filtrer directement sur ROW_NUMBER dans le WHERE

Une erreur très fréquente, y compris chez les développeurs expérimentés, est de vouloir utiliser l’alias de la colonne analytique directement dans la clause WHERE de la même requête :

-- ❌ INCORRECTE : provoque une erreur ORA-00904
SELECT
    ID_CLIENT,
    DATE_COMMANDE,
    ROW_NUMBER() OVER (PARTITION BY ID_CLIENT ORDER BY DATE_COMMANDE DESC) AS RN
FROM COMMANDES
WHERE RN = 1;  -- ORA-00904 : "RN" : identificateur non valide

Oracle évalue les fonctions analytiques après le WHERE, il est donc impossible de filtrer sur leur résultat dans la même passe. La solution est d’encapsuler la requête dans une sous-requête ou une CTE (Common Table Expression) :

-- ✅ CORRECTE : utilisation d'une CTE (WITH)
WITH COMMANDES_NUMEROTEES AS (
    SELECT
        ID_CLIENT,
        DATE_COMMANDE,
        MONTANT,
        ROW_NUMBER() OVER (PARTITION BY ID_CLIENT ORDER BY DATE_COMMANDE DESC) AS RN
    FROM COMMANDES
)
SELECT ID_CLIENT, DATE_COMMANDE, MONTANT
FROM COMMANDES_NUMEROTEES
WHERE RN = 1;

Résumé

Point clé	Détail
Type de fonction	Analytique (fenêtrage)
Numéros uniques	Toujours uniques, même en cas d’ex-æquo
`PARTITION BY`	Optionnel — redémarre le compteur par groupe
`ORDER BY` dans OVER	Obligatoire — définit l’ordre de numérotation
Filtrage possible	Uniquement en sous-requête ou CTE
Cas d’usage principaux	Pagination, dédoublonnage, Top N par groupe

2 bonnes pratiques Oracle

Privilégiez les CTE (WITH) aux sous-requêtes imbriquées : elles améliorent la lisibilité du code et facilitent la maintenance, surtout lorsque ROW_NUMBER() est combiné à d’autres fonctions analytiques.
Vérifiez toujours le comportement en cas d’ex-æquo dans l’ORDER BY : si deux lignes ont la même valeur de tri, Oracle attribue des numéros de façon arbitraire mais distincte. Pour un résultat déterministe, ajoutez une colonne discriminante (par exemple une clé primaire) en secondaire dans l’ORDER BY.

Aller plus loin

Vous maîtrisez maintenant ROW_NUMBER sous Oracle. Pour approfondir vos connaissances sur les fonctions analytiques et le SQL Oracle avancé, consultez ces articles complémentaires :

RANK et DENSE_RANK en Oracle — Comprenez les différences avec ROW_NUMBER et sachez quand utiliser chaque fonction de classement.
Les fonctions analytiques Oracle (OVER, PARTITION BY) — Un guide complet sur le fenêtrage SQL pour maîtriser LAG, LEAD, SUM analytique et bien d’autres.
La clause WITH (CTE) en Oracle — Apprenez à structurer des requêtes complexes avec les expressions de table communes pour un code plus lisible et performant.