ROWNUM ou ROW_NUMBER() : intervalle de lignes triées

Lorsque l'on affiche les résultats d'une requête dans une interface de logiciel, on peut avoir besoin de gérer la pagination, et donc le découpage du résultat par lots de lignes (ou intervalles). Pour ce faire, il faut retrouver les lignes n° tant à tant la plupart du temps au sein d'un ensemble trié.

Exemple :

On créé une table et des lignes avec valeurs insérées dans le désordre :
 

SQL> CREATE TABLE toto(id CHAR(1));

Table created.
 

SQL> INSERT INTO toto VALUES ('e');

1 row created.

SQL> INSERT INTO toto VALUES ('a');

1 row created.

SQL> INSERT INTO toto VALUES ('d');

1 row created.

SQL> INSERT INTO toto VALUES ('c');

1 row created.

SQL> INSERT INTO toto VALUES ('b');

1 row created.

 
On sélectionne ces lignes que l'on commence par trier suivant la colonne id, pour ne récupérer que celles qui ressortent de ce tri qu'entre la 2ème et 4ème position.

SQL> SELECT * FROM (
  2      SELECT ROWNUM num, id FROM (
  3          SELECT id FROM toto ORDER BY id
  4      )
  5  )
  6  WHERE num BETWEEN 2 AND 4;

       NUM I
---------- -
         2 b
         3 c
         4 d

 
On retrouve bien les valeurs b, c et d (ordre et intervalle recherchés). Ce qu'il faut retenir c'est que le tri doit être déjà fait avant de pouvoir sélectionner un intervalle par ROWNUM.

On peut également utiliser la fonction analytique ROW_NUMBER() OVER pour obtenir le même résultat.

Exemple :

SQL> SELECT *  FROM (
  2      SELECT ROW_NUMBER() OVER (ORDER BY id) num, id FROM toto
  3  )
  4  WHERE num BETWEEN 2 AND 4;

       NUM I
---------- -
         2 b
         3 c
         4 d