Numéroter les lignes du jeu de résultats


      Comment affecter un numéro à chaque ligne d'un jeu de résultats ?
      Il est assez fréquent de trouver dans les programmes exclusivement écrits pour traiter des données stockées dans Oracle Database le mot clé ROWNUM.
      L'utilisation de ROWNUM peut être complexe et c'est une source de bugs applicatifs. Je la déconseille avec Oracle Database depuis la version 12.1 puisque ce SGBD a intégré toutes les solutions standard SQL pour s'en passer. Mais il est malgré tout parfois nécessaire de connaître son comportement pour l'émuler avec PostgreSQL dans le cas d'une migration.
      Cette page ne traite pas de l'utilisation de rownum dans la clause de filtrage (WHERE) pour réaliser des requêtes TOP-N. Ce sujet est traité spécifiquement dans cette page avec un exemple de piège à éviter.
      Ici, nous allons nous intéresser à l'utilisation du mot clé ROWNUM dans la clause de projection (SELECT).
      Tout d'abord, une démonstration avec Oracle Database 18c :

Live SQL 18.3.3, running Oracle Database 18c Enterprise Edition - 18.1.0.0.0 create table geants(idge integer, nmge character varying(32)); insert into geants values(1, 'Oumpfor'); insert into geants values(2, 'Marouf'); insert into geants values(3, 'Barouf'); insert into geants values(5, 'Carouf'); insert into geants values(4, 'Darouf'); commit; select * from geants; IDGE NMGE 1 Oumpfor 2 Marouf 3 Barouf 5 Carouf 4 Darouf 5 rows selected. select rownum, idge, nmge from geants; ROWNUM IDGE NMGE 1 1 Oumpfor 2 2 Marouf 3 3 Barouf 4 5 Carouf 5 4 Darouf 5 rows selected. select rownum, idge, nmge from geants order by idge desc; ROWNUM IDGE NMGE 4 5 Carouf 5 4 Darouf 3 3 Barouf 2 2 Marouf 1 1 Oumpfor 5 rows selected. select rownum, idge, nmge from geants where mod(idge,2) = 1 order by idge desc; ROWNUM IDGE NMGE 3 5 Carouf 2 3 Barouf 1 1 Oumpfor 3 rows selected. select rownum, idge, nmge from (select idge, nmge from geants order by idge desc) s; ROWNUM IDGE NMGE 1 5 Carouf 2 4 Darouf 3 3 Barouf 4 2 Marouf 5 1 Oumpfor 5 rows selected. insert into geants values(1, 'Oumpfor'); select distinct rownum, idge, nmge from geants; ROWNUM IDGE NMGE 5 4 Darouf 1 1 Oumpfor 4 5 Carouf 6 1 Oumpfor 2 2 Marouf 3 3 Barouf 6 rows selected. select rownum, idge, nmge from (select distinct idge, nmge from geants order by idge desc) s; ROWNUM IDGE NMGE 1 5 Carouf 2 4 Darouf 3 3 Barouf 4 2 Marouf 5 1 Oumpfor 5 rows selected.


      Que pouvons-nous observer ? Le mot clé Oracle ROWNUM peut bien être utilisé en tant que pseudo colonne dans la clause de projection (SELECT). Cela permet d'attribuer un numéro à chaque ligne retournée, un entier unique en commençant par 1 puis 2 etc.
      Il est important de noter que ce numéro est affecté avant un éventuel tri. L'utilisation de rownum avec une clause ORDER BY peut ainsi surprendre si on s'attend à recevoir les rownum dans l'ordre 1, 2, 3 etc.
      Attention aussi à l'élimination des doublons avec distinct au niveau de la clause de projection. Le rownum affecté aux deux lignes (1, 'Oumpfor') sont différents donc les doublons ne sont pas éliminés avec la syntaxe "select distinct rownum, idge, nmge from geants".
      À présent, place à PostgreSQL. Comment numéroter les lignes d'un jeu de résultats et émuler rownum si c'est réellement nécessaire ?
      Démonstration avec PostgreSQL 9.6 :

select version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit create table geants(idge integer, nmge character varying(32)); insert into geants values(1, 'Oumpfor'); insert into geants values(2, 'Marouf'); insert into geants values(3, 'Barouf'); insert into geants values(5, 'Carouf'); insert into geants values(4, 'Darouf'); commit; table geants; idge | nmge ------+--------- 1 | Oumpfor 2 | Marouf 3 | Barouf 5 | Carouf 4 | Darouf (5 lignes) select rownum, idge, nmge from geants; ERREUR: la colonne « rownum » n'existe pas LIGNE 1 : select rownum, idge, nmge select row_number() over(), idge, nmge from geants; row_number | idge | nmge ------------+------+--------- 1 | 1 | Oumpfor 2 | 2 | Marouf 3 | 3 | Barouf 4 | 5 | Carouf 5 | 4 | Darouf (5 lignes) select row_number() over(), idge, nmge from geants order by idge desc; row_number | idge | nmge ------------+------+--------- 4 | 5 | Carouf 5 | 4 | Darouf 3 | 3 | Barouf 2 | 2 | Marouf 1 | 1 | Oumpfor (5 lignes) select row_number() over(), idge, nmge from geants where mod(idge,2) = 1 order by idge desc; row_number | idge | nmge ------------+------+--------- 3 | 5 | Carouf 2 | 3 | Barouf 1 | 1 | Oumpfor (3 lignes) select row_number() over(), idge, nmge from (select idge, nmge from geants order by idge desc) s; row_number | idge | nmge ------------+------+--------- 1 | 5 | Carouf 2 | 4 | Darouf 3 | 3 | Barouf 4 | 2 | Marouf 5 | 1 | Oumpfor (5 lignes) insert into geants values(1, 'Oumpfor'); select row_number() over(order by idge desc), idge, nmge from geants order by idge desc; row_number | idge | nmge ------------+------+--------- 1 | 5 | Carouf 2 | 4 | Darouf 3 | 3 | Barouf 4 | 2 | Marouf 5 | 1 | Oumpfor 6 | 1 | Oumpfor (6 lignes) select distinct row_number() over(), idge, nmge from geants; row_number | idge | nmge ------------+------+--------- 3 | 3 | Barouf 5 | 4 | Darouf 1 | 1 | Oumpfor 2 | 2 | Marouf 6 | 1 | Oumpfor 4 | 5 | Carouf (6 lignes) select row_number() over(), idge, nmge from (select distinct idge, nmge from geants order by idge desc) s; row_number | idge | nmge ------------+------+--------- 1 | 5 | Carouf 2 | 4 | Darouf 3 | 3 | Barouf 4 | 2 | Marouf 5 | 1 | Oumpfor (5 lignes)


      La fonction analytique row_number() peut être utilisée pour émuler la pseudocolonne rownum. Cette fonction affecte un numéro à chaque ligne de la partition en cours. Ici, nous n'avons pas partitionné puisque nous avons utilisé la syntaxe over (). L'ensemble du jeu de résultats constitue donc une partition et chacune de ses lignes est numérotée de manière unique en commençant par 1.
      Nous n'avons pas non plus demandé un ordre particulier au niveau de la partition, les lignes sont numérotées au fur et à mesure qu'elles sont lues. Le comportement de row_number over () est donc ici similaire au comportement de rownum.
      Il est bien sûr possible de numéroter les lignes en tenant compte d'un tri avec une sous-requête comme avec rownum mais aussi en ajoutant order by au niveau de la fenêtre.

      Est-il possible pour uniformiser le code d'utiliser row_number également avec Oracle ?
      Démonstration avec Oracle 18c :

Live SQL 18.3.3, running Oracle Database 18c Enterprise Edition - 18.1.0.0.0 select row_number() over(), idge, nmge from (select idge, nmge from geants order by idge desc) s; ORA-30485: missing ORDER BY expression in the window specification select row_number() over(order by idge desc) as nombre, idge, nmge from geants order by idge desc; nombre IDGE NMGE 1 5 Carouf 2 4 Darouf 3 3 Barouf 4 2 Marouf 5 1 Oumpfor 6 1 Oumpfor 6 rows selected.


      Avec Oracle Database, impossible d'utiliser row_number sans order by au niveau de la fenêtre. Il est néanmoins possible d'utiliser une syntaxe commune Oracle/PostgreSQL dans la majeure partie des cas fonctionnels si vous avez vraiment la contrainte de l'unicité du code.

Mise à jour : 22/09/2018