435 votes

Comment sélectionner la nième ligne dans une table de base de données SQL ?

Je suis intéressé par l'apprentissage de méthodes (idéalement) agnostiques par rapport à la base de données pour sélectionner l'image de la base de données. n d'une table de base de données. Il serait également intéressant de voir comment cela peut être réalisé en utilisant la fonctionnalité native des bases de données suivantes :

  • SQL Server
  • MySQL
  • PostgreSQL
  • SQLite
  • Oracle

Je fais actuellement quelque chose comme ce qui suit dans SQL Server 2005, mais je serais intéressé de voir les approches plus agnostiques des autres :

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000

Crédit pour le SQL ci-dessus : Le blog de Firoz Ansari

Mise à jour : Voir La réponse de Troels Arvin concernant la norme SQL. Troels, avez-vous des liens que nous pouvons citer ?

3 votes

Oui. Voici un lien vers des informations sur la norme ISO SQL : troels.arvin.dk/db/rdbms/links/#standards

17 votes

Je tiens à préciser que, selon la définition d'une relation, les lignes d'un tableau n'ont pas d'ordre, de sorte que la Nième ligne d'un tableau ne peut pas être sélectionnée. Ce qui peut être sélectionné est la Nième ligne d'un ensemble de lignes retourné par (le reste de) une requête, ce qui est ce que votre exemple et toutes les autres réponses accomplissent. Pour la plupart des gens, il s'agit d'une simple question de sémantique, mais elle met en évidence le problème sous-jacent de la question. Si vous avez besoin de retourner OrderNo N puis introduire un N° de séquence de commande dans le tableau et le générer à partir d'un générateur de séquence indépendant lors de la création d'une nouvelle commande.

2 votes

La norme SQL définit l'option offset x fetch first y rows only . Actuellement supporté par (au moins) Postgres, Oracle12, DB2.

372voto

Henrik Gustafsson Points 11755

Il y a des moyens de le faire dans des parties optionnelles de la norme, mais beaucoup de bases de données supportent leur propre façon de le faire.

Un très bon site qui traite de ce sujet et d'autres choses est le suivant http://troels.arvin.dk/db/rdbms/#select-limit .

Fondamentalement, PostgreSQL et MySQL supportent le non-standard :

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 et MSSQL prennent en charge les fonctions de fenêtrage standard :

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(que j'ai juste copié du site lié ci-dessus puisque je n'utilise jamais ces DB)

Mise à jour : Depuis PostgreSQL 8.4, les fonctions de fenêtrage standard sont prises en charge, et le deuxième exemple devrait donc fonctionner pour PostgreSQL également.

Mise à jour : SQLite a ajouté la prise en charge des fonctions de fenêtre dans la version 3.25.0 du 2018-09-15, de sorte que les deux formulaires fonctionnent également dans SQLite.

3 votes

MySQL utilise également la syntaxe OFFSET et LIMIT. Firebird utilise les mots-clés FIRST et SKIP, mais ils sont placés juste après SELECT.

10 votes

Cela ne devrait-il pas être WHERE rownumber = n pour obtenir la nième ligne seulement ?

0 votes

MySQL supporte les fonctions de fenêtre depuis la version 8. MariaDB depuis la version 10.2

114voto

Neall Points 12075

PostgreSQL prend en charge fonctions de fenêtrage tels qu'ils sont définis par la norme SQL, mais ils sont maladroits, de sorte que la plupart des gens utilisent (le non standard) LIMIT / OFFSET :

SELECT
    *
FROM
    mytable
ORDER BY
    somefield
LIMIT 1 OFFSET 20;

Cet exemple sélectionne la 21ème ligne. OFFSET 20 indique à Postgres d'ignorer les 20 premiers enregistrements. Si vous ne spécifiez pas un ORDER BY clause, il n'y a aucune garantie quant à l'enregistrement que vous récupérerez, ce qui est rarement utile.

32voto

Bill Williams Points 680

Je ne suis pas sûr des autres, mais je sais que SQLite et MySQL n'ont pas d'ordre de rangée "par défaut". Dans ces deux dialectes, au moins, l'extrait suivant récupère la quinzième entrée de the_table, en la triant par date et heure d'ajout :

SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15

(bien sûr, il faudrait avoir un champ DATETIME ajouté, et le définir à la date/heure à laquelle l'entrée a été ajoutée...)

0 votes

Cela semble être la meilleure façon de limiter la requête avec une valeur de décalage en ligne. Mais ne devrions-nous pas utiliser 0,14 ici ? 1,15 laissera la première ligne.

0 votes

Mais que signifie le 15 ? Je sais que le 1 indique qu'il faut obtenir un enregistrement. La virgule n'est pas utilisée dans l'exemple que j'ai vérifié. 1keydata.com/sql/sql-limit.html

2 votes

En fait, d'ici php.about.com/od/mysqlcommands/g/Limit_sql.htm Si vous voulez saisir la 15ème entrée, ne feriez-vous pas LIMIT 14, 1 (le 0ème est le premier élément, 1 de longueur) ?

26voto

Ben Breen Points 706

Cette fonction est intégrée dans les versions SQL 2005 et supérieures. Utilisez la fonction ROW_NUMBER(). Elle est excellente pour les pages web avec une navigation de style << Prev and Next >> :

Syntaxe :

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23

0 votes

Je préfère cette solution, car elle semble plus directe.

18voto

Tim Saunders Points 3694

Je pense que c'est très inefficace, mais c'est une approche assez simple, qui a fonctionné sur un petit ensemble de données sur lequel je l'ai essayé.

select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc

On obtiendrait le 5ème élément, en changeant le deuxième chiffre du haut pour obtenir un nème élément différent.

SQL server uniquement (je pense) mais devrait fonctionner sur les anciennes versions qui ne supportent pas ROW_NUMBER().

0 votes

Je vais l'utiliser car ROW_NUMBER() ne fonctionne pas en SQL 2000 (oui, nous avons un client sur SQL 2000 encore) Plus précisément, je vais remplacer le '5' avec une variable itérateur d'une boucle et l'utiliser pour copier et modifier chaque ligne d'une table à tour de rôle. Peut-être que quelqu'un verra ce commentaire et trouvera cela utile.

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X