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

ДОБАВИТЬ:

LIMIT n,1

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

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

Вот общая версия 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);

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

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

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

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

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

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

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

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

Когда мы работали с 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

Это было не изящно и не быстро, но работало.

Оракул:

select * from (select foo from bar order by foo) where ROWNUM = x

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

В Sybase SQL Anywhere:

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

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

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

Действительно хороший сайт, рассказывающий об этом и других вещах, - 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 от 15 сентября 2018 г., поэтому обе формы также работают в SQLite.

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

select *
from thetable
limit n-1, 1

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

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

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

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

невероятно, что вы можете найти 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

SELECT * FROM emp a
WHERE  n = (SELECT COUNT( _rowid)
              FROM emp b
             WHERE a. _rowid >= b. _rowid);

Для 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-й строке. Не забудьте после этого ввести rowcount 0.

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

Синтаксис:

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

T-SQL - Выбор N-го RecordNumber из таблицы

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

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

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

Просто возьмите МОД и измените число 10 здесь на любое число, которое хотите.

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

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

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

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

Далее, если вам нужна сумма, идентификатор или некоторый числовой порядок сортировки, вы можете использовать функцию CAST в MySQL.

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

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

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

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

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

Это даст вам 10-й РЯД таблицы emp!

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

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

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

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

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

SELECT * FROM (                        
   SELECT RRN(FOO) AS RRN, FOO.*
   FROM FOO                         
   ORDER BY RRN(FOO)) BAR             
 WHERE BAR.RRN = recordnumber

select * from 
(select * from ordered order by order_id limit 100) x order by 
x.order_id desc limit 1;

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

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

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

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

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

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

SELECT * 
FROM   sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

Для 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;

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

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 3

Если вы хотите посмотреть на собственные функции: MySQL, PostgreSQL, SQLite и Oracle (в основном SQL Server, похоже, не имеет этой функции) вы можете ДЕЙСТВИТЕЛЬНО использовать оконную функцию NTH_VALUE. Источник Oracle: Функции Oracle: NTH_VALUE

Я действительно экспериментировал с этим в нашей базе данных Oracle, чтобы провести некоторое сравнение первой строки (после заказа) со второй строкой (опять же, после заказа). Код будет выглядеть примерно так (если вы не хотите переходить по ссылке):

SELECT DISTINCT dept_id
     , NTH_VALUE(salary,2) OVER (PARTITION BY dept_id ORDER BY salary DESC
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
        AS "SECOND HIGHEST"
     , NTH_VALUE(salary,3) OVER (PARTITION BY dept_id ORDER BY salary DESC
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS "THIRD HIGHEST"
  FROM employees
 WHERE dept_id in (10,20)
 ORDER 
    BY dept_id;

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

Я немного опоздал на вечеринку, но я сделал это без необходимости использования окон или использования

WHERE x IN (...)

SELECT TOP 1
--select the value needed from t1
[col2]
FROM
(
   SELECT TOP 2 --the Nth row, alter this to taste
   UE2.[col1],
   UE2.[col2],
   UE2.[date],
   UE2.[time],
   UE2.[UID]
   FROM
   [table1] AS UE2
   WHERE
   UE2.[col1] = ID --this is a subquery 
   AND
   UE2.[col2] IS NOT NULL
   ORDER BY
   UE2.[date] DESC, UE2.[time] DESC --sorting by date and time newest first
) AS t1
ORDER BY t1.[date] ASC, t1.[time] ASC --this reverses the order of the sort in t1

Кажется, работает довольно быстро, хотя, честно говоря, у меня всего около 500 строк данных.

Это работает в MSSQL

WITH r AS (
  SELECT TOP 1000 * FROM emp
)
SELECT * FROM r
EXCEPT
SELECT TOP 999 FROM r

Это даст 1000-ю строку в SQL Server.