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

je suis intéressé à apprendre quelques (idéalement) méthodes agnostiques de base de données de sélection de la n e ligne à partir 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 suis en train de faire quelque chose comme ce qui suit dans SQL Server 2005, mais je serais intéressé de voir d'autres approches plus agnostiques:

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: Firoz Ansari's Weblog

mise à Jour: Voir Troels Arvin la réponse de concernant le standard SQL. Troels, avez-vous des liens que nous pouvons citer?

319
demandé sur Community 2008-08-19 21:13:11
la source

29 ответов

il y a des façons de faire cela dans les parties optionnelles de la norme, mais beaucoup de bases de données prennent en charge leur propre façon de le faire.

un très bon site qui parle de ceci et d'autres choses est http://troels.arvin.dk/db/rdbms/#select-limit .

en gros, PostgreSQL et MySQL supportent le non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 et MSSQL soutient 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 je viens de copier à partir du site lié ci-dessus puisque je n'utilise jamais ces DBs)

mise à jour: à partir de PostgreSQL 8.4 les fonctions de fenêtrage standard sont supportées, donc s'attendre à ce que le second exemple fonctionne aussi pour PostgreSQL.

286
répondu Henrik Gustafsson 2011-03-18 18:41:10
la source

le LIMIT / OFFSET syntaxe dans PostgreSQL est:

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

cet exemple sélectionne la 21ème ligne. OFFSET 20 dit à Postgres de sauter les 20 premiers enregistrements. Si vous ne spécifiez pas une clause ORDER BY , il n'y a aucune garantie sur l'enregistrement que vous récupérerez, ce qui est rarement utile.

apparemment, la norme SQL est muette sur la question de la limite en dehors des fonctions de fenêtrage fou, ce qui explique pourquoi tout le monde la met en œuvre différemment.

81
répondu Neall 2016-12-14 13:11:59
la source

Je ne suis pas sûr de tout le reste, mais je sais que SQLite et MySQL n'ont pas d'ordre de ligne" par défaut". Dans ces deux dialectes, au moins, le fragment suivant saisit la 15ème entrée de la table, en triant par la date / heure il a été ajouté:

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

(bien sûr, vous devez avoir un champ DATETIME ajouté, et le mettre à la date/heure que l'entrée a été ajoutée...)

25
répondu Ellen Teapot 2008-08-19 21:20:33
la source

SQL 2005 et au-dessus a cette caractéristique intégrée. Utilisez la fonction ROW_NUMBER (). Il est excellent pour les pages web avec un style de navigation << Précédent et suivant>>:

syntaxe:

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23
17
répondu Ben Breen 2016-12-14 13:16:27
la source

je soupçonne que c'est sauvagement inefficace, mais est une approche assez simple, qui a fonctionné sur un petit ensemble de données que je l'ai essayé sur.

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

cela obtiendrait le 5e élément, changer le deuxième numéro pour obtenir un nième élément différent

SQL server uniquement (je crois), mais devrait fonctionner sur des versions plus anciennes qui ne prennent pas en charge la fonction ROW_NUMBER().

16
répondu Tim Saunders 2008-08-19 21:34:38
la source

1 petit changement: n-1 au lieu de N.

select *
from thetable
limit n-1, 1
11
répondu Nick Berardi 2012-12-04 03:35:02
la source

le Vérifier sur SQL Server:

Select top 10 * From emp 
EXCEPT
Select top 9 * From emp

cela vous donnera la 10ème ligne de la table emp!

11
répondu Rameshwar Pawale 2015-04-09 23:25:14
la source

contrairement à ce que prétendent certaines réponses, la norme SQL n'est pas muette sur ce sujet.

depuis SQL: 2003, vous avez été en mesure d'utiliser des "fonctions de fenêtre" pour sauter les lignes et limiter les ensembles de résultats.

et dans SQL: 2008, une approche légèrement plus simple avait été ajoutée, en utilisant

OFFSET skip ROWS FETCH FIRST n ROWS ONLY

personnellement, je ne pense pas que L'ajout de SQL:2008 était vraiment nécessaire, donc si J'étais ISO, Je l'aurais gardé hors de déjà assez grand standard.

8
répondu Troels Arvin 2017-09-20 22:21:48
la source

Oracle:

select * from (select foo from bar order by foo) where ROWNUM = x
6
répondu Mark Harrison 2008-08-19 22:51:29
la source

lorsque nous travaillions au MSSQL 2000, nous faisions ce que nous appelions le "triple flip":

ÉDITÉ

DECLARE @InnerPageSize int
DECLARE @OuterPageSize int
DECLARE @Count int

SELECT @Count = COUNT(<column>) FROM <TABLE>
SET @InnerPageSize = @PageNum * @PageSize
SET @OuterPageSize = @Count - ((@PageNum - 1) * @PageSize)

IF (@OuterPageSize < 0)
    SET @OuterPageSize = 0
ELSE IF (@OuterPageSize > @PageSize)
    SET @OuterPageSize = @PageSize

DECLARE @sql NVARCHAR(8000)

SET @sql = 'SELECT * FROM
(
    SELECT TOP ' + CAST(@OuterPageSize AS nvarchar(5)) + ' * FROM
    (
        SELECT TOP ' + CAST(@InnerPageSize AS nvarchar(5)) + ' * FROM <TABLE> ORDER BY <column> ASC
    ) AS t1 ORDER BY <column> DESC
) AS t2 ORDER BY <column> ASC'

PRINT @sql
EXECUTE sp_executesql @sql

ce n'était pas élégant, et ce n'était pas rapide, mais ça a marché.

6
répondu Adam V 2011-12-30 19:31:24
la source

SQL SERVER


sélectionner n'importe quel enregistrement du haut

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n

sélectionner n'TH record à partir du bas

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n
6
répondu Aditya 2014-02-19 08:35:17
la source

Voici une solution rapide à votre confusion.

SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1

ici vous pouvez obtenir la dernière rangée en remplissant N=0, la deuxième dernière par N=1, La Quatrième dernière en remplissant N = 3 et ainsi de suite.

c'est une question très courante au cours de l'interview et C'est très simple.

plus si vous voulez montant, ID ou un ordre de tri numérique que vous pouvez aller pour la fonction CAST dans MySQL.

SELECT DISTINCT (`amount`) FROM cart ORDER BY CAST( `amount` AS SIGNED ) DESC LIMIT 4 , 1

ici par remplissage N = 4 Vous serez en mesure d'obtenir cinquième dernier Record du plus haut montant de la table de panier. Vous pouvez ajuster votre nom de champ et de table et trouver une solution.

3
répondu Amit Shah 2012-05-17 13:29:27
la source

ajouter:

LIMIT n,1

qui limitera les résultats à un résultat commençant par le résultat N.

2
répondu Andrew G. Johnson 2008-08-19 21:14:05
la source

LIMIT n, 1 ne fonctionne pas dans MS SQL Server. Je pense que c'est la seule base de données qui ne supporte pas cette syntaxe. Pour être juste, il ne fait pas partie de la norme SQL, bien qu'elle soit largement soutenu qu'il devrait être. En tout sauf SQL server LIMIT fonctionne très bien. Pour SQL server, Je n'ai pas pu trouver de solution élégante.

2
répondu Kibbee 2008-08-19 21:18:06
la source

Voici une version générique d'un sproc que j'ai récemment écrit pour Oracle qui permet le paging/tri dynamique-HTH

-- p_LowerBound = first row # in the returned set; if second page of 10 rows,
--                this would be 11 (-1 for unbounded/not set)
-- p_UpperBound = last row # in the returned set; if second page of 10 rows,
--                this would be 20 (-1 for unbounded/not set)

OPEN o_Cursor FOR
SELECT * FROM (
SELECT
    Column1,
    Column2
    rownum AS rn
FROM
(
    SELECT
        tbl.Column1,
        tbl.column2
    FROM MyTable tbl
    WHERE
        tbl.Column1 = p_PKParam OR
        tbl.Column1 = -1
    ORDER BY
        DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 1, Column1, 'X'),'X'),
        DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 1, Column1, 'X'),'X') DESC,
        DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate),
        DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate) DESC
))
WHERE
    (rn >= p_lowerBound OR p_lowerBound = -1) AND
    (rn <= p_upperBound OR p_upperBound = -1);
2
répondu Greg Hurlman 2008-08-19 21:19:01
la source

mais vraiment, n'est-ce pas juste des trucs de salon pour une bonne conception de base de données en premier lieu? Les quelques fois où j'ai eu besoin de cette fonctionnalité, c'était pour une simple requête off pour faire un rapport rapide. Pour tout vrai travail, utiliser des trucs comme ça, c'est attirer les ennuis. Si la sélection d'une ligne particulière est nécessaire, puis juste avoir une colonne avec une valeur séquentielle et être fait avec elle.

2
répondu 2008-08-19 23:06:57
la source

par exemple, si vous voulez sélectionner une dixième ligne dans MSSQL, vous pouvez utiliser;

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
  FROM TableName
) AS foo
WHERE rownumber % 10 = 0

prenez le MOD et changez le numéro 10 ici.

2
répondu E-A 2011-12-30 12:41:20
la source

dans Sybase SQL Anywhere:

SELECT TOP 1 START AT n * from table ORDER BY whatever

n'oubliez pas la commande ou elle n'a pas de sens.

1
répondu Graeme Perrow 2008-08-19 23:06:59
la source

pour SQL Server, une façon générique d'aller par numéro de ligne est comme tel: Mettez ROWCOUNT @row [email protected] = le numéro de la ligne sur laquelle vous souhaitez travailler.

Par Exemple:

mettre rowcount 20 --mettre row to 20th row

sélectionner viande, fromage de dbo.sandwich -- choisir les colonnes du tableau à la 20e ligne

set rowcount 0 --définit rowcount retour à toutes les lignes

ce sera le retour de la 20ème rangée information. Assurez-vous de mettre dans le rowcount 0 après.

je connais noobish, mais je suis un SQL noob et je l'ai utilisé alors que puis-je dire?

1
répondu 2009-06-22 21:00:08
la source

T-SQL - Sélection d'une N-ième RecordNumber à partir d'un Tableau

select * from
 (select row_number() over (order by Rand() desc) as Rno,* from TableName) T where T.Rno = RecordNumber

Where  RecordNumber --> Record Number to Select
       TableName --> To be Replaced with your Table Name

pour par exemple pour sélectionner 5 e enregistrement d'un employé de table, votre requête devrait être

select * from
 (select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5
1
répondu Sangeeth Krishna 2011-10-26 16:46:41
la source
SELECT * FROM emp a
WHERE  n = (SELECT COUNT( _rowid)
              FROM emp b
             WHERE a. _rowid >= b. _rowid);
1
répondu 2 revs, 2 users 62%Rahul Sharma 2012-03-20 19:48:43
la source
SELECT
    top 1 *
FROM
    table_name
WHERE
    column_name IN (
        SELECT
            top N column_name
        FROM
            TABLE
        ORDER BY
            column_name
    )
ORDER BY
    column_name DESC

j'ai écrit cette requête pour trouver Nième ligne. Exemple avec cette requête serait

SELECT
    top 1 *
FROM
    Employee
WHERE
    emp_id IN (
        SELECT
            top 7 emp_id
        FROM
            Employee
        ORDER BY
            emp_id
    )
ORDER BY
    emp_id DESC
1
répondu Arjun Chiddarwar 2016-12-14 12:52:07
la source

incroyable que vous puissiez trouver un moteur SQL exécutant celui-ci ...

WITH sentence AS
(SELECT 
    stuff,
    row = ROW_NUMBER() OVER (ORDER BY Id)
FROM 
    SentenceType
    )
SELECT
    sen.stuff
FROM sentence sen
WHERE sen.row = (ABS(CHECKSUM(NEWID())) % 100) + 1
0
répondu jrEving 2012-03-20 19:48:30
la source

rien de fantaisiste, pas de fonctions spéciales, au cas où vous utilisiez le cachet comme je le fais...

SELECT TOP 1 * FROM (
  SELECT TOP n * FROM <table>
  ORDER BY ID Desc
)
ORDER BY ID ASC

étant donné que vous avez une colonne ID ou une colonne datestamp en laquelle vous pouvez avoir confiance.

0
répondu Scott Beeson 2015-10-01 16:16:54
la source

C'est comme ça que je le ferais dans DB2 SQL, je crois que le RRN (numéro d'enregistrement relatif) est stocké dans la table par le O/S;

SELECT * FROM (                        
   SELECT RRN(FOO) AS RRN, FOO.*
   FROM FOO                         
   ORDER BY RRN(FOO)) BAR             
 WHERE BAR.RRN = recordnumber
0
répondu RDKells 2016-11-18 18:12:02
la source
select * from 
(select * from ordered order by order_id limit 100) x order by 
x.order_id desc limit 1;

sélectionnez d'abord les 100 premières rangées par ordre croissant, puis sélectionnez la dernière rangée par ordre décroissant et limitez à 1. Toutefois, il s'agit d'un énoncé très coûteux, car il donne accès aux données deux fois.

0
répondu Dwipam Katariya 2017-06-02 23:26:39
la source

Il me semble que, pour être efficace, vous devez 1) générer un nombre aléatoire entre 0 et un de moins que le nombre d'enregistrements de base de données, et 2) être en mesure de sélectionner la ligne à cette position. Malheureusement, différentes bases de données ont différents générateurs de nombres aléatoires et différentes façons de sélectionner une ligne à une position dans un jeu de résultats - habituellement vous spécifiez combien de lignes à sauter et combien de lignes vous voulez, mais il est fait différemment pour différentes bases de données. Voici quelque chose qui fonctionne pour me in SQLite:

select * 
from Table 
limit abs(random()) % (select count(*) from Words), 1;

cela dépend de la possibilité d'utiliser une sous-requête dans la clause limit (qui en SQLite est LIMIT ,). sélectionner le nombre d'enregistrements dans une table devrait être particulièrement efficace, faisant partie des métadonnées de la base de données, mais cela dépend de l'implémentation de la base de données. Aussi, je ne sais pas si la requête va réellement construire le résultat avant de récupérer le nth record, mais j'espère qu'il n'a pas besoin de le faire. Note que je ne spécifie pas une clause "ordre par". Il pourrait être préférable de "commander par" quelque chose comme la clé primaire, qui aura un index - obtenir l'enregistrement Nth à partir d'un index pourrait être plus rapide si la base de données ne peut pas obtenir l'enregistrement Nth à partir de la base de données elle-même sans construire le jeu de résultats.

0
répondu user1738579 2017-07-17 10:46:08
la source

dans Oracle 12c, vous pouvez utiliser OFFSET..FETCH..ROWS option avec ORDER BY

par exemple, pour obtenir le 3ème enregistrement de haut:

SELECT * 
FROM   sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
0
répondu Kaushik Nayak 2018-02-05 15:43:42
la source

pour SQL server, la ligne suivante retournera la première ligne de la table.

declare @rowNumber int = 1;
    select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
    select TOP(@rowNumber - 1) * from [dbo].[someTable];

vous pouvez boucler les valeurs avec quelque chose comme ceci:

WHILE @constVar > 0
BEGIN
    declare @rowNumber int = @consVar;
       select TOP(@rowNumber) * from [dbo].[someTable];
    EXCEPT
       select TOP(@rowNumber - 1) * from [dbo].[someTable];  

       SET @constVar = @constVar - 1;    
END;
0
répondu sony vizio 2018-06-21 18:45:35
la source

Autres questions sur sql postgresql mysql database oracle