Qui est au top ?

(sujet préalablement traité avec la version 9.4)

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, il est plus performant de 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.
      Nous allons ici considérer une table contenant 10 lancers, il s'agit de ramener les 3 meilleurs, triés par performance décroissante :

Connecte a : Oracle Database 11g Release 11.2.0.4.0 - 64bit Production create table lancers(dtl date, idg integer, perf integer); insert into lancers values(current_date - 100, 10, 22000); insert into lancers values(current_date - 95, 7, 44000); insert into lancers values(current_date - 91, 8, 17000); insert into lancers values(current_date - 89, 11, 71000); insert into lancers values(current_date - 87, 12, 77000); insert into lancers values(current_date - 85, 5, 71000); insert into lancers values(current_date - 77, 1, 10000); insert into lancers values(current_date - 62, 2, 11000); insert into lancers values(current_date - 52, 13, 90000); insert into lancers values(current_date - 13, 7, 22000); insert into lancers values(current_date - 9, 15, 33000); select * from lancers where rownum < 4 order by perf desc; DTL IDG PERF -------- ---------- ---------- 16/01/20 7 44000 11/01/20 10 22000 20/01/20 8 17000

      Cela renvoie un résultat mais pas du tout celui attendu. Le tri est opéré APRES le filtrage par rownum. Nous avons donc renvoyé les 3 premiers résultats qui venaient puis ils ont été triés.
      Voici l'écriture correcte avec rownum :

select * from (select * from lancers order by perf desc) lancerstries where rownum < 4; DTL IDG PERF -------- ---------- ---------- 28/02/20 13 90000 24/01/20 12 77000 22/01/20 11 71000

      Cette écriture n'est pas standard SQL de toute façon. Comment écrire cette requête en SQL ? La norme SQL-2008 donne la réponse : utilisez FETCH FIRST n ROW(S) ONLY. Illustration avec PostgreSQL 13 beta mais PostgreSQL respecte la norme sur ce point depuis la version 8.4 sortie en 2009 :
select version(); version ----------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 13beta1 (Ubuntu 13~beta1-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit select * from lancers order by perf desc fetch first 3 rows only; dtl | idg | perf ------------+-----+------- 2020-02-28 | 13 | 90000 2020-01-24 | 12 | 77000 2020-01-22 | 11 | 71000 (3 lignes)

      Microsoft SQL Server comprend cette syntaxe depuis la version 2012 et Oracle Database depuis la version 12.1. Illustration avec Oracle 19c :
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 select * from lancers order by perf desc fetch first 3 rows only; DTL IDG PERF --------- ---------- ---------- 28-FEB-20 13 90000 24-JAN-20 12 77000 22-JAN-20 11 71000

      Vous pouvez souhaiter avoir les 3 meilleures performances mais connaître les ex aequo au dernier rang. Pour cela, PostgreSQL intègre depuis la version 13 la syntaxe "WITH TIES" à utiliser au lieu de "ONLY". Illustration avec PostgreSQL 13 beta :
select * from lancers order by perf desc fetch first 3 rows with ties; dtl | idg | perf ------------+-----+------- 2020-02-28 | 13 | 90000 2020-01-24 | 12 | 77000 2020-01-26 | 5 | 71000 2020-01-22 | 11 | 71000 (4 lignes)

      Cette syntaxe est également connue d'Oracle Database depuis la version 12.1, illustration toujours avec Oracle 19c :
select * from lancers order by perf desc fetch first 3 rows with ties; DTL IDG PERF --------- ---------- ---------- 28-FEB-20 13 90000 24-JAN-20 12 77000 22-JAN-20 11 71000 26-JAN-20 5 71000

      Voilà, vous avez tous les moyens d'écrire vos requêtes top-N avec du code SQL standard compris par de nombreux SGBD.

Mise à jour : 21/05/2020