SQL TOP, FETCH FIRST et WITH TIES

Leçon 2.9 · Temps de lecture : ~9 min · Exercices pratiques ↓

SQL TOP et FETCH FIRST n ROWS ONLY sont les alternatives à LIMIT : TOP est utilisé par SQL Server, et FETCH FIRST est le standard SQL pris en charge par PostgreSQL, Oracle, MariaDB et d'autres. Dans cette leçon, vous apprendrez la syntaxe standard, la pagination avec OFFSET ... FETCH, l'option WITH TIES qui garde les lignes de valeurs égales, PERCENT, ainsi que les anciennes méthodes d'Oracle et de Firebird.

Dans la leçon 2.7, vous avez limité les résultats avec LIMIT et OFFSET. Cette syntaxe fonctionne dans MySQL, MariaDB, PostgreSQL et SQLite, mais SQL Server et Oracle ne l'acceptent pas. Quand vous changez de SGBD, lisez les requêtes des autres ou préparez un entretien, vous rencontrerez plusieurs autres façons d'écrire la même idée.

Pourquoi limiter les lignes a plusieurs syntaxes

Pendant longtemps, la limitation des lignes ne faisait pas partie du standard SQL, et chaque SGBD a inventé sa propre syntaxe :

• MySQL et PostgreSQL ont ajouté LIMIT, et SQLite l'a repris.
• SQL Server a ajouté TOP.
• Oracle avait la pseudo-colonne ROWNUM.
• Firebird a ajouté FIRST et SKIP.

La clause standard FETCH FIRST n'est apparue qu'avec SQL:2008, quand ces habitudes étaient déjà installées. Aujourd'hui, la plupart des SGBD prennent en charge la forme standard en plus de la leur, mais MySQL et SQLite toujours pas.

FETCH FIRST n ROWS ONLY : la syntaxe standard

FETCH FIRST se place après ORDER BY, là où serait LIMIT :

SELECT title, length
FROM film
ORDER BY length
FETCH FIRST 3 ROWS ONLY;

Résultat : trois des films les plus courts, de 46 minutes. La requête fonctionne dans MariaDB 10.6+, PostgreSQL et Oracle ; MySQL signale une erreur de syntaxe.

Les mots FIRST et NEXT, ROW et ROWS sont interchangeables : FETCH NEXT 1 ROW ONLY signifie la même chose que FETCH FIRST 1 ROWS ONLY. Choisissez ce qui se lit le mieux. Sans nombre, FETCH FIRST ROW ONLY renvoie une ligne.

Paginer avec OFFSET ... FETCH NEXT

La forme standard d'OFFSET s'écrit avec le mot ROWS et se place avant FETCH :

SELECT title, length
FROM film
ORDER BY length, title
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Résultat : les films 11 à 15 par durée, comme LIMIT 5 OFFSET 10.

C'est la seule façon de paginer dans SQL Server, et elle y exige ORDER BY : OFFSET sans tri est une erreur de syntaxe.

WITH TIES : garder les lignes qui partagent la dernière valeur

Revenons aux films les plus courts. Cinq films durent 46 minutes, mais FETCH FIRST 3 ROWS ONLY n'en renvoie que trois, et lesquels, c'est le SGBD qui décide.

WITH TIES règle ce problème : il renvoie les N premières lignes plus toutes les lignes suivantes qui ont les mêmes valeurs de tri que la ligne N.

SELECT title, length
FROM film
ORDER BY length
FETCH FIRST 3 ROWS WITH TIES;

Résultat : 5 lignes. Les cinq films de 46 minutes sont renvoyés, car le troisième partage sa durée avec deux autres.

WITH TIES répond honnêtement à des questions comme « les trois films les plus courts » : aucun film n'est écarté simplement parce qu'il partage une valeur avec le dernier.

Règles de WITH TIES

• Il exige ORDER BY : les égalités sont définies par les colonnes de tri. Sans tri, PostgreSQL et MariaDB signalent une erreur.
• Une colonne de départage supprime les égalités. Avec ORDER BY length, title, aucune ligne n'est égale à une autre, et WITH TIES renvoie exactement 3 lignes.
• Le résultat peut être bien plus grand que N. Les films les plus longs de Sakila durent 185 minutes, et ORDER BY length DESC FETCH FIRST 3 ROWS WITH TIES renvoie 10 films.

La même requête dans PostgreSQL sur la base Bookings trouve les itinéraires les plus longs :

SELECT flight_no, departure_city, arrival_city, duration
FROM routes
ORDER BY duration DESC
FETCH FIRST 3 ROWS WITH TIES;

Résultat : 4 itinéraires de 8 heures 50 : les vols entre Moscou et Ioujno-Sakhalinsk ou Petropavlovsk.

WITH TIES dans FETCH est pris en charge par PostgreSQL 13+, MariaDB 10.6+ et Oracle 12c+.

SQL Server : TOP

SQL Server place TOP juste après SELECT :

SELECT TOP (3) Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Résultat : trois des produits les plus chers d'AdventureWorks.

Les parenthèses sont facultatives pour un nombre (TOP 3 fonctionne aussi), mais recommandées : avec elles, TOP accepte aussi une variable ou une expression, comme TOP (@n).

TOP a son propre WITH TIES, et il ne fonctionne qu'avec TOP : dans SQL Server, OFFSET ... FETCH ne prend pas en charge WITH TIES.

SELECT TOP (3) WITH TIES Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Résultat : 5 produits. Cinq tailles du vélo Road-150 Red coûtent toutes 3578.27.

Pièges de TOP

• TOP sans ORDER BY est autorisé, mais renvoie des lignes arbitraires, tout comme LIMIT sans tri.
• TOP ... WITH TIES sans ORDER BY est une erreur.
• SQL Server n'accepte pas FETCH FIRST seul : FETCH n'est permis qu'après OFFSET. Pour les premières lignes, écrivez OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY ou utilisez TOP.

Renvoyer un pourcentage de lignes

Parfois, il ne faut pas 10 lignes mais 10 % d'entre elles. SQL Server et Oracle savent le faire directement :

SELECT TOP (1) PERCENT Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Résultat : 3 lignes. 1 % de 295 produits fait 2,95, et SQL Server arrondit à l'entier supérieur.

Oracle utilise la forme standard :

SELECT first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 5 PERCENT ROWS ONLY;

Résultat : les 6 employés les mieux payés : 5 % de 107 fait 5,35, arrondi à l'entier supérieur.

DuckDB écrit le pourcentage dans LIMIT : LIMIT 10%. PostgreSQL, MariaDB et MySQL ne gèrent pas les pourcentages : il faut d'abord compter les lignes et calculer le nombre vous-même.

Oracle : ROWNUM et FETCH FIRST

Avant la version 12c, Oracle n'avait pas FETCH FIRST, et l'ancien code limite les lignes avec la pseudo-colonne ROWNUM. Elle numérote les lignes au fur et à mesure de leur lecture, avant l'application d'ORDER BY. C'est pourquoi cette requête est fausse :

SELECT first_name, last_name, salary
FROM hr.employees
WHERE ROWNUM <= 3
ORDER BY salary;

Résultat : non pas les trois salaires les plus bas, mais les trois premières lignes lues (17000, 17000 et 24000), triées ensuite.

Pour trier d'abord, l'ancien code enveloppe la requête triée dans une sous-requête et applique ROWNUM à son résultat :

SELECT first_name, last_name, salary
FROM (
    SELECT first_name, last_name, salary
    FROM hr.employees
    ORDER BY salary
)
WHERE ROWNUM <= 3;

Résultat : les trois salaires les plus bas : 2100, 2200 et 2200.

Autre piège : WHERE ROWNUM > 1 ne renvoie aucune ligne. La première ligne reçoit le numéro 1, échoue à la condition et est écartée ; la suivante redevient la numéro 1, et ainsi de suite.

Dans Oracle 12c et les versions suivantes, utilisez simplement FETCH FIRST, y compris avec WITH TIES :

SELECT first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 7 ROWS WITH TIES;

Résultat : 8 employés : le 7e et le 8e gagnent tous deux 12008.

Oracle n'accepte toujours pas LIMIT, même en version 26ai.

Firebird : FIRST, SKIP et ROWS

Firebird a deux syntaxes à lui. FIRST et SKIP se placent juste après SELECT, comme TOP :

SELECT FIRST 3 SKIP 2 first_name, last_name, salary
FROM employee
ORDER BY salary DESC;

Résultat : les employés 3 à 5 par salaire : deux lignes sont sautées et trois sont renvoyées.

ROWS m TO n se place à la fin de la requête et prend des numéros de ligne au lieu d'un nombre de lignes :

SELECT first_name, last_name, salary
FROM employee
ORDER BY salary DESC
ROWS 3 TO 5;

Résultat : les mêmes employés 3 à 5. ROWS 3 seul signifie les trois premières lignes.

Depuis Firebird 3.0, le standard OFFSET ... FETCH fonctionne aussi. WITH TIES n'est pas pris en charge, même dans Firebird 5.

WITH TIES dans MySQL et SQLite

MySQL et SQLite n'ont ni FETCH ni WITH TIES, mais le même résultat peut être construit avec des outils que vous apprendrez plus loin dans le cours. En voici un aperçu.

Une sous-requête trouve la valeur de la troisième ligne, et la requête externe prend tous les films qui ne sont pas plus longs :

SELECT title, length
FROM film
WHERE length <= (
    SELECT length
    FROM film
    ORDER BY length
    LIMIT 1 OFFSET 2
)
ORDER BY length, title;

Résultat : les mêmes 5 films de 46 minutes qu'avec FETCH FIRST 3 ROWS WITH TIES. Les sous-requêtes sont traitées dans la leçon 6.2.

La fonction de fenêtrage RANK() donne le même rang aux valeurs égales, donc rnk <= 3 garde toutes les égalités :

SELECT title, length
FROM (
    SELECT title, length, RANK() OVER (ORDER BY length) AS rnk
    FROM film
) AS ranked
WHERE rnk <= 3
ORDER BY length, title;

Résultat : les mêmes 5 films. RANK() est traité dans la leçon 7.2.

Syntaxe de limitation des lignes selon le SGBD

Si une requête doit fonctionner dans plusieurs SGBD, le standard OFFSET ... FETCH avec ORDER BY en couvre le plus : tous sauf MySQL et SQLite. Avec cette leçon, vous terminez les bases de la récupération des lignes ; le module suivant montre comment transformer les valeurs avec les fonctions SQL.

Points clés :

• FETCH FIRST n ROWS ONLY est l'alternative standard à LIMIT ; MySQL et SQLite ne la prennent pas en charge.
• OFFSET m ROWS FETCH NEXT n ROWS ONLY pagine les résultats ; dans SQL Server, il exige ORDER BY.
• WITH TIES ajoute les lignes qui partagent les valeurs de tri de la dernière ligne, donc le résultat peut dépasser N.
• SQL Server utilise TOP (n), TOP (n) WITH TIES et TOP (n) PERCENT.
• ROWNUM d'Oracle est attribué avant ORDER BY : triez d'abord dans une sous-requête ou utilisez FETCH FIRST en 12c+.
• Dans MySQL et SQLite, construisez WITH TIES avec une sous-requête ou avec RANK().

Questions fréquentes

Quel est l'équivalent de LIMIT dans SQL Server ?

Pour les premières lignes, utilisez SELECT TOP (n) ... ; pour paginer, ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY. SQL Server n'accepte pas LIMIT et ne permet pas FETCH sans OFFSET.

Que fait WITH TIES en SQL ?

Il renvoie les N premières lignes plus chaque ligne suivante ayant les mêmes valeurs dans les colonnes d'ORDER BY que la ligne N. FETCH FIRST 3 ROWS WITH TIES peut renvoyer 3, 5 ou 10 lignes, selon le nombre de lignes qui partagent la troisième valeur.

MySQL prend-il en charge FETCH FIRST ou WITH TIES ?

Non. MySQL 8 et 9 ne prennent en charge que LIMIT. MariaDB prend en charge FETCH FIRST ... WITH TIES depuis la version 10.6. Dans MySQL, obtenez le même résultat avec une sous-requête ou avec RANK().

Comment limiter les lignes dans Oracle ?

Dans Oracle 12c et les versions suivantes, utilisez FETCH FIRST n ROWS ONLY après ORDER BY. Dans les anciennes versions, triez dans une sous-requête et filtrez son résultat avec WHERE ROWNUM <= n. Oracle ne prend pas en charge LIMIT.

Quelle est la différence entre FETCH FIRST et FETCH NEXT ?

Aucune : FIRST et NEXT sont synonymes, tout comme ROW et ROWS. Par convention, on écrit FETCH FIRST sans OFFSET et FETCH NEXT après lui.