Qui est au top ?

(sujet mis à jour avec la version 13 beta)

ROWNUM, LIMIT, FETCH FIRST n ROW(S) ONLY

      Il est parfois intéressant de limiter le nombre de résultats retournés par le SGBD pour une requête particulière. Certes, il est toujours possible de demander le jeu de résultats complet puis de n’afficher que les premiers résultats. Mais, si un ensemble fixé de résultats est requis et que l’on se moque des autres résultats, autant faire le travail en amont.
      Les utilisateurs d’Oracle utilisent souvent pour cela ROWNUM, une pseudo-colonne attribuant un numéro à chaque ligne du jeu de résultats. Ajouter rownum < 4 à votre clause WHERE permettra ainsi de ne renvoyer que 3 résultats.
      ROWNUM est spécifique Oracle mais avec d’autres SGBD, comme d’ailleurs PostgreSQL, vous utilisez parfois le mot clé LIMIT. Toutes ces syntaxes ne sont pas standard SQL. Pour revenir à ROWNUM, une mauvaise compréhension de son fonctionnement peut même entraîner un problème de résultats faux.
      Je vais partir d’un exemple que j’ai rencontré en production sur un site. Pour traiter une pile de dossiers une requête Oracle renvoyait LE dossier le plus urgent à traiter. Au départ il s’agissait d’un problème de performance (pourquoi ne traiter qu’un dossier à la fois ?) mais il y avait plus grave. Illustration avec un exemple simplifié :

SQL*Plus: Release 11.2.0.2.0 Production on Dim. Mars 27 13:44:26 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production create table dossiers(id_dossier number, ouvert char(1), priorite number); Table created. insert into dossiers(id_dossier, ouvert, priorite) values(1, 'f', 1); 1 row created. insert into dossiers(id_dossier, ouvert, priorite) values(2, 'f', 1); 1 row created. insert into dossiers(id_dossier, ouvert, priorite) values(3, 't', 2); 1 row created. insert into dossiers(id_dossier, ouvert, priorite) values(4, 't', 3); 1 row created. insert into dossiers(id_dossier, ouvert, priorite) values(5, 't', 1); 1 row created. commit; Commit complete. select id_dossier from dossiers where ouvert = 't' and rownum = 1 order by priorite desc; ID_DOSSIER ---------- 3 select id_dossier from (select id_dossier from dossiers where ouvert = 't' order by priorite desc) where rownum = 1; ID_DOSSIER ---------- 4

      Ouille ! Le résultat attendu était "4" et il a fallu utiliser une sous-requête pour l’obtenir. Le tri est en effet opéré APRÈS l’application du filtrage via le ROWNUM comme Tom Kyte l’explique de manière approfondie dans cet article. Malheureusement c’était la première syntaxe qui était utilisée en production. Le moteur retournait une ligne puis triait cette unique ligne. Les dossiers ouverts étaient tout de même traités, mais pas dans l’ordre souhaité.
      Mais comment écrire cette requête en SQL alors ? La norme SQL-2008 donne la réponse : utilisez FETCH FIRST n ROW(S) ONLY. Illustration avec PostgreSQL qui respecte la norme sur ce point depuis la version 8.4 sortie en 2009 :

select version(); version ----------------------------------------------------------------------------------------------- PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 ligne) create table dossiers(id_dossier integer, ouvert boolean, priorite smallint); CREATE TABLE insert into dossiers(id_dossier, ouvert, priorite) values(1, false, 1); INSERT 0 1 insert into dossiers(id_dossier, ouvert, priorite) values(2, false, 1); INSERT 0 1 insert into dossiers(id_dossier, ouvert, priorite) values(3, true, 2); INSERT 0 1 insert into dossiers(id_dossier, ouvert, priorite) values(4, true, 3); INSERT 0 1 insert into dossiers(id_dossier, ouvert, priorite) values(5, true, 1); INSERT 0 1 select id_dossier from dossiers where ouvert = true order by priorite desc limit 1; id_dossier ------------ 4 (1 ligne) select id_dossier from dossiers where ouvert = true order by priorite desc fetch first 1 row only; id_dossier ------------ 4 (1 ligne) select id_dossier from dossiers where ouvert = true order by priorite desc fetch first 1 rows only; id_dossier ------------ 4 (1 ligne)

      Le tour est joué, le résultat attendu est obtenu avec une syntaxe simple et standard que vous pouvez d’ailleurs utiliser aussi avec Oracle Database à partir de la version 12.1 ou Microsoft SQL Server à partir de la version 2012. Oubliez les ROWNUM et les LIMIT !

Mise à jour : 27/03/2016