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

Question

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 ?

Answer 1

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.

Answer 2

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.

Answer 3

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...)

Answer 4

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

Answer 5

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().