Как выбрать n-ую строку в таблице базы данных SQL?

Я заинтересован в изучении некоторых (в идеале) независимых от базы данных способов выбора n- й строки из таблицы базы данных. Также было бы интересно увидеть, как этого можно достичь, используя встроенную функциональность следующих баз данных:

  • SQL Server
  • MySQL
  • PostgreSQL
  • SQLite
  • оракул

В настоящее время я делаю что-то вроде следующего в SQL Server 2005, но мне было бы интересно увидеть другие более независимые подходы:

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

Кредит на вышеупомянутый SQL: блог Фироза Ансари

Обновление: см . Ответ Troels Arvin относительно стандарта SQL. Troels, есть ли у вас ссылки, которые мы можем цитировать?

19.08.2008 17:13:11
Да. Вот ссылка на информацию о стандарте ISO SQL: troels.arvin.dk/db/rdbms/links/#standards
Troels Arvin 31.10.2008 07:10:17
Просто чтобы указать, что по определению отношения строки в таблице не имеют порядка, поэтому N-ую строку в таблице нельзя выбрать. Что может быть выбрано, так это N-ая строка в наборе строк, возвращаемая (остальной частью) запроса, что и выполняет ваш пример и все остальные ответы. Для большинства это может быть просто семантикой, но это указывает на основную проблему вопроса. Если вам нужно вернуться OrderNo N, введите столбец OrderSequenceNo в таблицу и сгенерируйте его из независимого генератора последовательностей при создании нового заказа.
Damir Sudarevic 26.10.2011 13:21:03
Стандарт SQL определяет опцию offset x fetch first y rows only. В настоящее время поддерживается (как минимум) Postgres, Oracle12, DB2.
a_horse_with_no_name 4.06.2016 13:28:44
30 ОТВЕТОВ
РЕШЕНИЕ

Есть способы сделать это в дополнительных частях стандарта, но многие базы данных поддерживают свой собственный способ сделать это.

Действительно хороший сайт, который рассказывает об этом и других вещах, это http://troels.arvin.dk/db/rdbms/#select-limit .

В основном, PostgreSQL и MySQL поддерживают нестандартные:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 и MSSQL поддерживают стандартные оконные функции:

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

(который я только что скопировал с сайта, указанного выше, поскольку я никогда не использую эти БД)

Обновление: Начиная с PostgreSQL 8.4 поддерживаются стандартные оконные функции, так что ожидайте, что второй пример будет работать и для PostgreSQL.

Обновление: SQLite добавил поддержку оконных функций в версии 3.25.0 на 2018-09-15, поэтому обе формы также работают в SQLite.

344
27.02.2019 15:52:24
MySQL также использует синтаксис OFFSET и LIMIT. Firebird использует ключевые слова FIRST и SKIP, но они размещаются сразу после SELECT.
Doug 1.12.2011 17:03:42
Разве это не должно быть, WHERE rownumber = nчтобы получить только n-й ряд?
Steve Bennett 12.09.2017 07:14:57
MySQL поддерживает оконные функции начиная с версии 8. MariaDB начиная с версии 10.2
Paul Spiegel 25.07.2019 20:01:20

ДОБАВИТЬ:

LIMIT n,1

Это ограничит результаты одним результатом, начиная с результата n.

3
19.08.2008 17:14:05

LIMIT n, 1 не работает в MS SQL Server. Я думаю, что это единственная крупная база данных, которая не поддерживает этот синтаксис. Честно говоря, он не является частью стандарта SQL, хотя он настолько широко поддерживается, что так и должно быть. Во всем, кроме SQL-сервера LIMIT прекрасно работает. Для сервера SQL я не смог найти элегантного решения.

2
19.08.2008 17:18:06
За исключением Oracle, DB2 - это почти каждая база данных корпоративного уровня во всем мире. PostgreSQL - это единственная корпоративная база данных, которая поддерживает ключевое слово LIMIT, и это главным образом потому, что, будучи открытым исходным кодом, он должен быть доступен для игнорируемой ACID толпы MySQL.
David 6.03.2009 13:39:02
@AlexD Этот «ответ» был опубликован еще в старые времена Stackoverflow до того, как были реализованы комментарии. Я бы опубликовал это как комментарий к другому ответу, но в то время комментариев не было.
Kibbee 28.08.2012 16:33:40

Вот общая версия sproc, которую я недавно написал для Oracle, которая допускает динамическое разбиение на страницы / сортировку - 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
19.08.2008 17:19:01

Я не уверен ни в одном из остальных, но я знаю, что SQLite и MySQL не имеют порядка строк по умолчанию. По крайней мере, на этих двух диалектах следующий фрагмент извлекает 15-ую запись из таблицы, сортируя по дате / времени ее добавления:

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

(конечно, вам нужно иметь добавленное поле DATETIME и установить в нем дату / время добавления записи ...)

29
19.08.2008 17:20:33
Это выглядит как лучший способ ограничить запрос встроенным значением смещения. Но разве мы не должны использовать 0,14 здесь? 1,15 выйдет из первого ряда.
Gladiator 23.07.2014 13:11:17
Что означает 15, хотя? Я знаю, что 1 говорит, чтобы получить одну запись. Запятая не используется в примере , который я проверил 1keydata.com/sql/sql-limit.html
committedandroider 6.02.2015 06:25:22
На самом деле, отсюда php.about.com/od/mysqlcommands/g/Limit_sql.htm , если вы хотите получить 15- ую запись, вы не выполните LIMIT 14, 1 (0-й - первый элемент, 1 длины
committedandroider 6.02.2015 06:27:46
это будет ВЫБРАТЬ * ИЗ the_table ORDER BY добавлен DESC LIMIT 15,1
JerryGoyal 27.04.2016 06:28:15

Я подозреваю, что это крайне неэффективно, но это довольно простой подход, который работал на небольшом наборе данных, на котором я его пробовал.

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

Это позволит получить 5-й элемент, изменить второй верхний номер, чтобы получить другой n-й элемент

Только SQL-сервер (я думаю), но должен работать на старых версиях, которые не поддерживают ROW_NUMBER ().

18
19.08.2008 17:34:38
Я собираюсь использовать это, так как ROW_NUMBER () не работает в SQL 2000 (да, у нас все еще есть клиент на SQL 2000) В частности, я собираюсь заменить '5' на переменную цикла итератора и использовать что копировать и модифицировать каждую строку таблицы по очереди. Может быть, кто-то увидит этот комментарий и найдет его полезным
Inversus 17.05.2013 20:02:37

PostgreSQL поддерживает оконные функции, как определено стандартом SQL, но они неудобны, поэтому большинство людей используют (нестандартные) LIMIT/OFFSET :

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

Этот пример выбирает 21-ую строку. OFFSET 20говорит Postgres пропустить первые 20 записей. Если вы не укажете ORDER BYпункт, нет гарантии, какую запись вы вернете, что редко бывает полезно.

100
16.10.2019 13:13:41

Когда мы работали в MSSQL 2000, мы делали то, что называли «трипл-флип»:

отредактированный

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

Это не было элегантно, и это не было быстро, но это работало.

7
30.12.2011 15:31:24
Допустим, у вас есть 25 строк, и вы хотите, чтобы третья страница имела размер 10 строк, то есть строки 21-25. Самый внутренний запрос получает верхние 30 строк (строки 1-25). Средний запрос получает последние 10 строк (строки 25-16). Внешний запрос переупорядочивает их и возвращает строки 16-25. Это явно неправильно, если вы хотели строки 21-25.
Bill Karwin 30.12.2011 08:53:54
Теперь это не работает, если нам нужна средняя страница. Скажем, у нас есть 25 строк, и мы хотим вторую страницу, то есть строки 11-20. Внутренний запрос получает верхние 2 * 10 = 20 строк или строки 1-20. Средний запрос получает последние 15 строк: 25 - ((2-1) * 10) = 15, что дает строки 20-6. Последний запрос меняет порядок и возвращает строки 6-20. Этот метод не работает, если общее количество строк не кратно желаемому размеру страницы.
Bill Karwin 30.12.2011 17:14:17
Возможно, лучший вывод заключается в том, что мы должны обновить все оставшиеся экземпляры MS SQL Server 2000. :-) Это почти 2012 год, и эта проблема была решена в течение многих лет лучше!
Bill Karwin 30.12.2011 17:15:38
@ Билл Карвин: Обратите внимание на IF / ELSE IFблоки под OuterPageSizeвычислением - на страницах 1 и 2 они OuterPageSizeвернут значение обратно до 10. На странице 3 (строки 21-25) вычисление вернёт 5, а на всех страницах 4 и выше, отрицательный результат расчета будет заменен на 0 (хотя, вероятно, было бы просто быстрее вернуть пустую строку данных сразу в этот момент).
Adam V 1.01.2012 01:50:47
О, теперь я вижу. Ну, я придерживаюсь своего мнения, что использование MS SQL Server 2000 сегодня не стоит проблем.
Bill Karwin 1.01.2012 02:57:45

Oracle:

select * from (select foo from bar order by foo) where ROWNUM = x
5
19.08.2008 18:51:29
where ROWNUM = xбудет работать только для х = 1 в БД Oracle. т.е. where ROWNUM = 2не вернет никаких строк.
aff 11.07.2019 01:18:42

Но на самом деле, разве все это на самом деле не является просто уловками для хорошего проектирования баз данных? Несколько раз я нуждался в такой функциональности, как простой запрос для быстрого создания отчета. Для любой реальной работы использование подобных трюков создает проблемы. Если требуется выбрать конкретную строку, просто создайте столбец с последовательным значением и покончите с этим.

2
19.08.2008 19:06:57

В Sybase SQL Anywhere:

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

Не забывайте ORDER BY или это бессмысленно.

1
19.08.2008 19:06:59

1 небольшое изменение: n-1 вместо n.

select *
from thetable
limit n-1, 1
15
3.12.2012 23:35:02
какие технологии?
user230910 15.01.2018 01:21:13

Вопреки тому, что утверждают некоторые ответы, стандарт SQL не молчит по этому вопросу.

Начиная с SQL: 2003, вы можете использовать «оконные функции» для пропуска строк и ограничения результирующих наборов.

А в SQL: 2008 был добавлен немного более простой подход, использующий
OFFSET skip ROWS FETCH FIRST n ROWS ONLY

Лично я не думаю, что добавление SQL: 2008 действительно было необходимо, поэтому, если бы я был ISO, я бы не использовал его в уже достаточно большом стандарте.

11
20.09.2017 19:21:48
Приятно, что есть стандарт, который облегчает жизнь людям, таким как я, и так приятно от Microsoft делать вещи стандартным образом :)
user230910 15.01.2018 02:08:15

невероятно, что вы можете найти движок SQL, выполняющий этот ...

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
20.03.2012 15:48:30
SELECT * FROM emp a
WHERE  n = (SELECT COUNT( _rowid)
              FROM emp b
             WHERE a. _rowid >= b. _rowid);
1
20.03.2012 15:48:43

Для SQL Server общий способ определения номера строки таков:

SET ROWCOUNT @row --@row = the row number you wish to work on.

Например:

set rowcount 20   --sets row to 20th row

select meat, cheese from dbo.sandwich --select columns from table at 20th row

set rowcount 0   --sets rowcount back to all rows

Это вернет информацию 20-го ряда. Обязательно укажите количество строк 0 после этого.

3
7.04.2019 12:52:07

В SQL 2005 и выше эта функция встроена. Используйте функцию ROW_NUMBER (). Отлично подходит для веб-страниц со стилем просмотра «Предыдущая и Следующая»:

Синтаксис:

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23
25
14.12.2016 10:16:27
Я предпочитаю это решение, так как оно кажется более прямым.
FoxArc 4.12.2019 15:37:25

T-SQL - выбор N-го номера записи из таблицы

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

Например, чтобы выбрать 5-ю запись из таблицы Employee, ваш запрос должен быть

select * from
 (select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5
1
26.10.2011 12:46:41

Например, если вы хотите выбрать каждую десятую строку в MSSQL, вы можете использовать;

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

Просто возьмите мод и измените номер 10 здесь на любой номер, который вы хотите.

3
30.12.2011 08:41:20

Вот быстрое решение вашей путаницы.

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

Здесь Вы можете получить Последнюю строку, Заполнив N = 0, Второй последний - N = 1, Четвертый Последний, Заполнив N = 3 и так далее.

Это очень распространенный вопрос во время интервью, и это очень просто.

Далее Если вы хотите Amount, ID или какой-либо Числовой Порядок сортировки, чем вы можете пойти для функции CAST в MySQL.

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

Здесь, заполнив N = 4, вы сможете получить пятую последнюю запись наивысшей суммы из таблицы CART. Вы можете выбрать свое поле и имя таблицы и найти решение.

4
17.05.2012 09:29:27

SQL SERVER


Выберите n-ю запись сверху

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

выберите n-ную запись снизу

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n
6
19.02.2014 04:35:17

Проверьте это на SQL Server:

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

Это даст вам 10-ю строку таблицы emp!

13
9.04.2015 20:25:14
Вы уже предоставили ответ на этот вопрос здесь. Удалите тот ответ, который, по вашему мнению, является неправильным. Если вы считаете, что оба ответа верны, то опубликуйте оба ответа в одном месте
SpringLearner 16.10.2014 11:04:18
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

Я написал этот запрос для поиска N-й строки. Пример с этим запросом будет

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
14.12.2016 09:52:07

Ничего особенного, никаких специальных функций, если вы используете Caché, как я ...

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

Учитывая, что у вас есть столбец ID или столбец с датой, которому можно доверять.

0
1.10.2015 13:16:54

Это то, как я бы делал это в DB2 SQL, я считаю, что RRN (относительный номер записи) хранится в таблице O / S;

SELECT * FROM (                        
   SELECT RRN(FOO) AS RRN, FOO.*
   FROM FOO                         
   ORDER BY RRN(FOO)) BAR             
 WHERE BAR.RRN = recordnumber
0
18.11.2016 15:12:02
select * from 
(select * from ordered order by order_id limit 100) x order by 
x.order_id desc limit 1;

Сначала выберите первые 100 строк, упорядочив их по возрастанию, а затем выберите последнюю строку, упорядочив по убыванию и ограничиваясь 1. Однако это очень дорогой оператор, поскольку он обращается к данным дважды.

0
2.06.2017 20:26:39

Мне кажется, что для эффективности вам необходимо: 1) сгенерировать случайное число от 0 до единицы меньше, чем количество записей в базе данных, и 2) иметь возможность выбрать строку в этой позиции. К сожалению, разные базы данных имеют разные генераторы случайных чисел и разные способы выбора строки в позиции в наборе результатов - обычно вы указываете, сколько строк пропустить и сколько строк вы хотите, но это делается по-разному для разных баз данных. Вот то, что работает для меня в SQLite:

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

Это зависит от возможности использовать подзапрос в предложении limit (в SQLite это LIMIT <recs to skip>, <recs to take>). Выбор количества записей в таблице должен быть особенно эффективным, поскольку он является частью базы данных. метаданные, но это зависит от реализации базы данных. Кроме того, я не знаю, будет ли запрос фактически формировать набор результатов перед извлечением N-й записи, но я надеюсь, что в этом нет необходимости. Обратите внимание, что я не определяю предложение "order by". Может быть, лучше «упорядочить» что-то вроде первичного ключа, который будет иметь индекс - получение N-й записи из индекса может быть быстрее, если база данных не может получить N-ую запись из самой базы данных без построения набора результатов. ,

0
17.07.2017 07:46:08

В Oracle 12c вы можете использовать OFFSET..FETCH..ROWS опцию сORDER BY

Например, чтобы получить третью запись сверху:

SELECT * 
FROM   sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
5
5.02.2018 12:43:42

Для SQL-сервера следующее вернет первую строку из заданной таблицы.

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

Вы можете перебирать значения с помощью чего-то вроде этого:

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;
2
21.06.2018 15:45:35

Наиболее подходящий ответ, который я видел в этой статье для сервера SQL

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 3
0
22.11.2019 20:52:46