Como seleccionar a n-ésima linha numa tabela de bases de dados SQL?
Estou interessado em aprender algumas (idealmente) maneiras agnósticas da base de dados de seleccionar a n th linha a partir de uma tabela de base de dados. Também seria interessante ver como isso pode ser alcançado usando a funcionalidade nativa das seguintes bases de dados:
- servidor SQL
- MySQL
- PostgreSQL
- SQLite
- Oráculo
WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000
Crédito pelo SQL acima: o Weblog da Firoz Ansari
actualização: ver a resposta de Troels Arvin sobre a norma SQL. Troels, tens alguma ligação que possamos citar?
29 answers
Um site realmente bom que fala sobre isso e outras coisas é http://troels.arvin.dk/db/rdbms/#select-limit.
Basicamente, o PostgreSQL e o MySQL suportam o não-padrão:
SELECT...
LIMIT y OFFSET x
Oracle, DB2 e MSSQL suportam as funções de janelas padrão:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
(que acabei de copiar do site ligado acima, uma vez que nunca use esses DBs)
Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.
A LIMIT
/ OFFSET
a sintaxe em PostgreSQL é:
SELECT
*
FROM
mytable
ORDER BY
somefield
LIMIT 1 OFFSET 20;
Este exemplo selecciona a 21ª linha. Está a dizer ao Postgres para saltar os primeiros 20 discos. Se você não especificar uma cláusula ORDER BY
, não há nenhuma garantia que registro você vai receber de volta, o que raramente é útil.
Não tenho a certeza sobre o resto, mas sei que o SQLite e o MySQL não têm nenhum pedido de linha "padrão". Nesses dois dialetos, pelo menos, o seguinte trecho agarra a décima quinta entrada da mesa, ordenando pela data / hora que foi adicionada:
SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15
(claro, você precisa ter um campo DATETIME adicionado, e configurá-lo para a data/hora que o item foi adicionado...)
O SQL 2005 e superior tem esta funcionalidade incorporada. Use a função ROW_ numere (). É excelente para páginas web com uma navegação de estilo >:
Sintaxe:
SELECT
*
FROM
(
SELECT
ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
*
FROM
Table_1
) sub
WHERE
RowNum = 23
select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc
Isto iria obter o quinto item, mudar o segundo número de topo para obter um item diferente
Só o servidor SQL (penso), mas deve trabalhar em versões mais antigas que não suportam o ROW_NUMBER().
1 pequena alteração: n-1 em vez de n.
select *
from thetable
limit n-1, 1
Verifique no servidor SQL:
Select top 10 * From emp
EXCEPT
Select top 9 * From emp
Isto vai dar-te a 10ª fila da mesa emp!
Desde SQL: 2003, você tem sido capaz de usar "funções de janela" para saltar linhas e limitar os conjuntos de resultados.
E em SQL:2008, foi acrescentada uma abordagem ligeiramente mais simples, utilizando
OFFSET skip ROWS
FETCH FIRST n ROWS ONLY
Oráculo:
select * from (select foo from bar order by foo) where ROWNUM = x
Edição
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
Não era elegante e não era rápido, mas funcionou.
SERVIDOR SQL
Seleccione o n ' ésimo registo do topo
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
Seleccione o n ' ésimo registo do fundo
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1
Aqui você pode obter a última linha preenchendo N=0, o segundo último por N=1, o quarto último preenchendo N = 3 e assim por diante.
Esta é uma questão muito comum na entrevista e é muito simples.Além disso, se quiser quantidade, ID ou alguma ordem de ordenação numérica, O u poderá ir para a função CAST no MySQL.
SELECT DISTINCT (`amount`) FROM cart ORDER BY CAST( `amount` AS SIGNED ) DESC LIMIT 4 , 1
Aqui preenchendo N = 4, poderá obter o quinto último registo da quantidade mais elevada da tabela CART. Você pode caber seu campo e nome da tabela e chegar a uma solução.
Adicionar:
LIMIT n,1
Isso irá limitar os resultados a um resultado a partir do resultado n.
O limite n, 1 não funciona no servidor de MS SQL. Acho que é a única base de dados importante que não suporta essa sintaxe. Para ser justo, não faz parte do padrão SQL, embora seja tão amplamente apoiado que deveria ser. Em tudo, exceto o limite de servidor SQL funciona muito bem. Para o servidor SQL, eu não fui capaz de encontrar uma solução elegante.
Aqui está uma versão genérica de um sproc que escrevi recentemente para a Oracle que permite uma chamada de pessoas/ordenação dinâmica-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);
Por exemplo, se quiser seleccionar todas as 10 linhas em MSSQL, pode usar;
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
FROM TableName
) AS foo
WHERE rownumber % 10 = 0
Pegue no MOD e mude o número 10 para o número que quiser.
In Sybase SQL Anywhere:
SELECT TOP 1 START AT n * from table ORDER BY whatever
Não te esqueças da ordem ou não faz sentido.
Para O servidor SQL, uma forma genérica de ir pelo número da linha é como tal: Define o número do ROWCOUNT @row --@row = o número da linha em que deseja trabalhar.
Por Exemplo:
Mudar o número de linhas 20 --muda a linha para a 20ª linha
Seleccionar carne, queijo do dbo.sandwich -- select columns from table at 20th row
Definir o número de linhas 0 --configura o número de linhas de volta para todas as linhas
Isto vai devolver a informação da 20ª fila. Não se esqueça de colocar o número 0 depois. Conheço o noobish, mas sou. um SQL noob e eu usámo-lo, o que posso dizer?T-SQL-seleccionar o n'. º número de Registo de uma tabela
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
Para, por exemplo, seleccionar 5. o registo de um empregado de mesa, a sua consulta deve ser
select * from
(select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5
SELECT * FROM emp a
WHERE n = (SELECT COUNT( _rowid)
FROM emp b
WHERE a. _rowid >= b. _rowid);
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
Escrevi esta pergunta para encontrar a Nth row.
Exemplo com esta consulta seria
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
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 TOP 1 * FROM (
SELECT TOP n * FROM <table>
ORDER BY ID Desc
)
ORDER BY ID ASC
Dado que tem uma coluna de ID ou uma coluna de datestamp em que pode confiar.
É assim que eu o faria dentro do DB2 SQL, acredito que o RRN (número de registo relativo) é armazenado dentro da tabela pelo o / S;
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;
Primeiro seleccione as 100 linhas de topo ordenando em ascendente e depois seleccione a última linha ordenando em descendente e limite para 1. No entanto, esta é uma declaração muito cara, uma vez que ele acessar os dados duas vezes.
select *
from Table
limit abs(random()) % (select count(*) from Words), 1;
Ele depende de ser capaz de usar uma subquery na cláusula limite (que em SQLite é limite
No Oracle 12c, pode usar a opção OFFSET..FETCH..ROWS
com ORDER BY
Por exemplo, para obter o 3º disco do topo:
SELECT *
FROM sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
Para o servidor SQL, o seguinte irá devolver a primeira linha de dar a tabela.
declare @rowNumber int = 1;
select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
select TOP(@rowNumber - 1) * from [dbo].[someTable];
Você pode percorrer os valores com algo como isto:
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;