Como selecionar a enésima linha em uma tabela de database SQL?

Estou interessado em aprender algumas formas (idealmente) de database agnóstico de selecionar a enésima linha a partir de uma tabela de database. Também seria interessante ver como isso pode ser alcançado usando a funcionalidade nativa dos seguintes bancos de dados:

  • servidor SQL
  • MySQL
  • PostgreSQL
  • SQLite
  • Oráculo

Atualmente estou fazendo algo como o seguinte no SQL Server 2005, mas eu estaria interessado em ver outras abordagens mais agnósticas:

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

Crédito para o SQL acima: Weblog de Firoz Ansari

Atualização: Veja a resposta da Troels Arvin sobre o padrão SQL. Troels, você tem algum link que possamos citar?

Existem maneiras de fazer isso em partes opcionais do padrão, mas muitos bancos de dados suportam sua própria maneira de fazê-lo.

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 vinculado acima, pois nunca uso esses DBs)

Atualização: A partir do PostgreSQL 8.4, as funções de janelas padrão são suportadas, então espere que o segundo exemplo funcione também para o PostgreSQL.

A syntax LIMIT / OFFSET no PostgreSQL é:

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

Este exemplo seleciona a 21ª linha. OFFSET 20 está dizendo ao Postgres para pular os 20 primeiros registros. Se você não especificar uma ORDER BY , não há garantia de qual registro você receberá de volta, o que raramente é útil.

Aparentemente, o padrão SQL é silencioso sobre o problema do limite, fora das funções loucas de janelamento, e é por isso que todos o implementam de maneira diferente.

Não tenho certeza sobre nenhum dos outros, mas sei que o SQLite e o MySQL não têm nenhuma ordenação de linha “padrão”. Nesses dois dialetos, pelo menos, o snippet a seguir agarra a 15ª input da tabela_, classificando pela data / hora em que foi adicionada:

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

(claro, você precisaria ter um campo DATETIME adicional e defini-lo como a data / hora em que a input foi adicionada …)

O SQL 2005 e superior tem esse recurso embutido. Use a function ROW_NUMBER (). É excelente para páginas da web com uma navegação no estilo < < Anterior e Próximo >>:

Sintaxe:

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

Eu suspeito que isso é descontroladamente ineficiente, mas é uma abordagem bastante simples, que funcionou em um pequeno dataset que eu experimentei.

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

Isso levaria o quinto item, altere o segundo número superior para obter um item diferente

SQL Server apenas (eu acho), mas deve funcionar em versões mais antigas que não suportam ROW_NUMBER ().

1 pequena alteração: n-1 em vez de n.

 select * from thetable limit n-1, 1 

Verifique no SQL Server:

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

Isso lhe dará a 10ª LINHA da tabela emp!

Ao contrário do que algumas das respostas afirmam, o padrão SQL não é silencioso em relação a esse assunto.

Desde o SQL: 2003, você tem conseguido usar “funções de janela” para pular linhas e limitar conjuntos de resultados.

E no SQL: 2008, uma abordagem um pouco mais simples foi adicionada, usando
OFFSET skip ROWS FETCH FIRST n ROWS ONLY

Pessoalmente, eu não acho que a adição do SQL: 2008 fosse realmente necessária, então se eu fosse ISO, eu teria mantido isso fora de um padrão já bastante grande.

Oráculo:

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

Quando costumávamos trabalhar no MSSQL 2000, fizemos o que chamamos de “triple-flip”:

EDITADO

 DECLARE @InnerPageSize int DECLARE @OuterPageSize int DECLARE @Count int SELECT @Count = COUNT() FROM  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 
ORDER BY ASC ) AS t1 ORDER BY DESC ) AS t2 ORDER BY ASC' PRINT @sql EXECUTE sp_executesql @sql

Não era elegante e não era rápido, mas funcionava.

SERVIDOR SQL


Selecione n’th registro de cima

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

selecione n’th registro da parte inferior

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

Aqui está uma solução rápida da sua confusão.

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

Aqui você pode obter a última linha preenchendo N = 0, a segunda última por N = 1, quarta por preencher N = 3 e assim por diante.

Esta é uma pergunta muito comum sobre a entrevista e é muito simples.

Além disso, se você quiser um valor, ID ou alguma ordem de sorting numérica, você pode usar a function CAST no MySQL.

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

Aqui, preenchendo N = 4 Você será capaz de obter o quinto último registro de maior valor da tabela CART. Você pode ajustar seu nome de campo e tabela e encontrar uma solução.

ADICIONAR:

 LIMIT n,1 

Isso limitará os resultados a um resultado começando no resultado n.

LIMIT n, 1 não funciona no MS SQL Server. Eu acho que é apenas o único grande database que não suporta essa syntax. Para ser justo, não faz parte do padrão SQL, embora seja tão amplamente suportado que deveria ser. Em tudo, exceto o SQL server, o LIMIT funciona muito bem. Para o SQL Server, não consegui encontrar uma solução elegante.

Aqui está uma versão genérica de um sproc que eu escrevi recentemente para Oracle que permite a paginação / 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); 

Mas realmente, não é tudo isso apenas truques de salão para um bom design de database em primeiro lugar? Nas poucas vezes em que precisei de funcionalidade como essa, era para uma consulta simples e simples fazer um relatório rápido. Para qualquer trabalho real, usar truques como esse é um convite a problemas. Se a seleção de uma linha específica for necessária, basta ter uma coluna com um valor sequencial e fazer isso.

Por exemplo, se você quiser selecionar todas as 10 linhas no MSSQL, poderá usar;

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

Apenas pegue o MOD e mude o número 10 aqui qualquer número que você quiser.

No Sybase SQL Anywhere:

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

Não se esqueça do ORDER BY ou é sem sentido.

Para o SQL Server, uma maneira genérica de ir pelo número da linha é a seguinte: SET ROWCOUNT @row – @ row = o número da linha em que você deseja trabalhar.

Por exemplo:

set rowcount 20 – define a linha para a 20ª linha

selecione carne, queijo de dbo.sandwich – selecione as colunas da tabela na 20ª fileira

set rowcount 0 – configura o número de linhas de volta para todas as linhas

Isso retornará as informações da 20ª linha. Não se esqueça de colocar o número de linhas 0 depois.

Eu sei noobish, mas eu sou um noob SQL e eu usei então o que posso dizer?

T-SQL – Selecionando N’th RecordNumber 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 

Por exemplo, para selecionar o 5º registro de uma tabela Employee, 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 

Eu escrevi esta consulta para encontrar a linha Nth. 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 

inacreditável que você pode encontrar um motor SQL executando este …

 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 

Nada extravagante, sem funções especiais, caso você use o Caché como eu faço …

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

Dado que você tem uma coluna de ID ou uma coluna de data e data em que pode confiar.

É assim que eu faria dentro do DB2 SQL, eu acredito que o RRN (número de registro 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, selecione as 100 primeiras linhas fazendo o pedido em ordem crescente e, em seguida, selecione a última linha, fazendo o pedido em ordem decrescente e limite em 1. No entanto, essa é uma afirmação muito cara, pois ela acessa os dados duas vezes.

Parece-me que, para ser eficiente, você precisa 1) gerar um número random entre 0 e um menor que o número de registros do database e 2) ser capaz de selecionar a linha nessa posição. Infelizmente, diferentes bancos de dados têm diferentes geradores de números randoms e diferentes maneiras de selecionar uma linha em uma posição em um conjunto de resultados – geralmente você especifica quantas linhas deseja ignorar e quantas linhas deseja, mas é feito de forma diferente para diferentes bancos de dados. Aqui está algo que funciona para mim no SQLite:

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

Depende de poder usar uma subconsulta na cláusula de limite (que no SQLite é LIMIT , ). A seleção do número de registros em uma tabela deve ser particularmente eficiente, sendo parte do database. metadados, mas isso depende da implementação do database. Além disso, não sei se a consulta realmente construirá o conjunto de resultados antes de recuperar o N-ésimo registro, mas espero que não seja necessário. Observe que não estou especificando uma cláusula “order by”. Pode ser melhor “ordenar por” algo como a chave primária, que terá um índice – obter o registro Nth de um índice pode ser mais rápido se o database não puder obter o registro Nth do próprio database sem criar o conjunto de resultados .

No Oracle 12c, você pode usar a opção OFFSET..FETCH..ROWS com ORDER BY

Por exemplo, para obter o terceiro registro do topo:

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

Para o servidor SQL, o seguinte retornará a primeira linha da 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 parecido com 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;