¿Cómo seleccionar la enésima fila en una tabla de base de datos SQL?
Estoy interesado en aprender algunas (idealmente) formas agnósticas de seleccionar la n fila de una tabla de base de datos. También sería interesante ver cómo se puede lograr esto utilizando la funcionalidad nativa de las siguientes bases de datos:
- SQL Server
- MySQL
- PostgreSQL
- SQLite
- Oracle
Actualmente estoy haciendo algo como lo siguiente en SQL Server 2005, pero estaría interesado en ver a otros más agnósticos enfoques:
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 el SQL anterior: Weblog de Firoz Ansari
Actualización: Ver La respuesta de Troels Arvin con respecto al estándar SQL. Troels, ¿tienes algún enlace que podamos citar?
29 answers
Hay formas de hacer esto en las partes opcionales del estándar, pero muchas bases de datos soportan su propia forma de hacerlo.
Un sitio realmente bueno que habla de esto y otras cosas es http://troels.arvin.dk/db/rdbms/#select-limit .
Básicamente, PostgreSQL y MySQL soportan lo no estándar:
SELECT...
LIMIT y OFFSET x
Oracle, DB2 y MSSQL soportan las funciones de ventana estándar:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
(que acabo de copiar del sitio enlazado anteriormente ya que nunca uso esos DBs)
Update: A partir de PostgreSQL 8.4 las funciones de ventana estándar son soportadas, así que espere que el segundo ejemplo funcione también para PostgreSQL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-03-18 15:41:10
El LIMIT
/ OFFSET
la sintaxis en PostgreSQL es:
SELECT
*
FROM
mytable
ORDER BY
somefield
LIMIT 1 OFFSET 20;
Este ejemplo selecciona la fila 21. OFFSET 20
le está diciendo a Postgres que se salte los primeros 20 registros. Si no especifica una cláusula ORDER BY
, no hay garantía de qué registro obtendrá, lo que rara vez es útil.
Aparentemente el estándar SQL no dice nada sobre el problema del límite fuera de las funciones locas de ventanas, por lo que todos lo implementan de manera diferente.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-14 10:11:59
No estoy seguro del resto, pero sé que SQLite y MySQL no tienen ningún orden de filas "predeterminado". En esos dos dialectos, al menos, el siguiente fragmento toma la entrada 15 de the_table, ordenando por la fecha/hora en que se agregó:
SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15
(por supuesto, necesitará tener un campo de FECHA y HORA agregado, y establecerlo en la fecha/hora en que se agregó la entrada...)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-08-19 17:20:33
SQL 2005 y superiores tiene esta característica incorporada. Utilice la función ROW_NUMBER (). Es excelente para páginas web con un estilo de navegación >:
Sintaxis:
SELECT
*
FROM
(
SELECT
ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
*
FROM
Table_1
) sub
WHERE
RowNum = 23
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-14 10:16:27
Sospecho que esto es tremendamente ineficiente, pero es un enfoque bastante simple, que funcionó en un pequeño conjunto de datos que probé.
select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc
Esto obtendría el 5to elemento, cambie el segundo número superior para obtener un n-ésimo elemento diferente
Solo SQL server (creo) pero debería funcionar en versiones anteriores que no soporten ROW_NUMBER().
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-08-19 17:34:38
1 pequeño cambio: n-1 en lugar de n.
select *
from thetable
limit n-1, 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-12-03 23:35:02
Verifíquelo en SQL Server:
Select top 10 * From emp
EXCEPT
Select top 9 * From emp
¡Esto te dará la décima FILA de la tabla emp!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-04-09 20:25:14
Contrariamente a lo que afirman algunas de las respuestas, el estándar SQL no guarda silencio con respecto a este tema.
Desde SQL:2003, puede usar "funciones de ventana" para omitir filas y limitar conjuntos de resultados.
Y en SQL: 2008, se había añadido un enfoque ligeramente más simple, utilizando
OFFSET skip ROWS
FETCH FIRST n ROWS ONLY
Personalmente, no creo que la adición de SQL:2008 fuera realmente necesaria, así que si yo fuera ISO, lo habría mantenido fuera de un estándar ya bastante grande.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-09-20 19:21:48
Oráculo:
select * from (select foo from bar order by foo) where ROWNUM = x
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-08-19 18:51:29
Cuando solíamos trabajar en MSSQL 2000, hicimos lo que llamamos el "triple-flip":
EDITADO
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
No era elegante, y no era rápido, pero funcionó.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-12-30 15:31:24
SQL SERVER
Seleccione n ' th registro de la parte superior
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
Seleccione n ' th registro desde abajo
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-02-19 04:35:17
Aquí está una solución rápida de su confusión.
SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1
Aquí puede obtener la Última fila llenando N=0, la penúltima por N=1, la Cuarta Última Llenando N=3 y así sucesivamente.
Esta es una pregunta muy común en la entrevista y es muy simple.
Además, si desea Cantidad, ID o algún Orden de clasificación Numérico que u puede ir para la función CAST en MySQL.
SELECT DISTINCT (`amount`) FROM cart ORDER BY CAST( `amount` AS SIGNED ) DESC LIMIT 4 , 1
Aquí rellenando N = 4 Usted será capaz de obtener el Quinto Último Registro de la Cantidad Más Alta de la tabla del CARRITO. Usted puede caber su nombre del campo y de la tabla y venir para arriba con la solución.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-17 09:29:27
AÑADIR:
LIMIT n,1
Que limitará los resultados a un resultado a partir del resultado n.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-08-19 17:14:05
EL LÍMITE n,1 no funciona en MS SQL Server. Creo que es la única base de datos importante que no soporta esa sintaxis. Para ser justos, no es parte del estándar SQL, aunque está tan ampliamente soportado que debería serlo. En todo excepto SQL server LIMIT funciona muy bien. Para SQL Server, no he sido capaz de encontrar una solución elegante.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-08-19 17:18:06
Aquí hay una versión genérica de un sproc que escribí recientemente para Oracle que permite la paginación dinámica / clasificación - 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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-08-19 17:19:01
Pero en realidad, ¿no es todo esto realmente solo trucos de salón para un buen diseño de bases de datos en primer lugar? Las pocas veces que necesitaba una funcionalidad como esta era para una simple consulta única para hacer un informe rápido. Para cualquier trabajo real, usar trucos como estos es invitar a problemas. Si se necesita seleccionar una fila en particular, simplemente tenga una columna con un valor secuencial y termine con ella.
Por ejemplo, si desea seleccionar cada fila 10 en MSSQL, puede usar;
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
FROM TableName
) AS foo
WHERE rownumber % 10 = 0
Simplemente tome el MOD y cambie el número 10 aquí cualquier número que desee.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-12-30 08:41:20
En Sybase SQL Anywhere:
SELECT TOP 1 START AT n * from table ORDER BY whatever
No se olvide de la ORDEN POR o no tiene sentido.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-08-19 19:06:59
Para SQL Server, una forma genérica de ir por número de fila es como tal: ESTABLECER ROWCOUNT @row @ @ row = el número de fila en el que desea trabajar.
Por Ejemplo:
Set rowcount 20 sets establece la fila a la fila 20
Seleccione carne, queso de dbo.sandwich select seleccionar columnas de la tabla en la fila 20
Set rowcount 0 sets devuelve rowcount a todas las filas
Esto devolverá la información de la fila 20. Asegúrese de poner en el rowcount 0 después.
Sé noobish, pero soy un SQL noob y yo lo hemos usado así que ¿qué puedo decir?
T-SQL-Selección del N'ésimo número de registro de una Tabla
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 ejemplo, para seleccionar el registro 5 de un empleado de la tabla, su consulta debe ser
select * from
(select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-26 12:46:41
SELECT * FROM emp a
WHERE n = (SELECT COUNT( _rowid)
FROM emp b
WHERE a. _rowid >= b. _rowid);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-03-20 15:48:43
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
He escrito esta consulta para encontrar la Enésima fila. Ejemplo con esta consulta sería
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-14 09:52:07
Increíble que pueda encontrar un motor SQL ejecutando 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-03-20 15:48:30
Nada elegante, ninguna función especial, en caso de que uses Caché como yo...
SELECT TOP 1 * FROM (
SELECT TOP n * FROM <table>
ORDER BY ID Desc
)
ORDER BY ID ASC
Dado que tiene una columna ID o una columna datestamp en la que puede confiar.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-10-01 13:16:54
Así es como lo haría dentro de DB2 SQL, creo que el RRN (número de registro relativo) se almacena dentro de la tabla por el O/S;
SELECT * FROM (
SELECT RRN(FOO) AS RRN, FOO.*
FROM FOO
ORDER BY RRN(FOO)) BAR
WHERE BAR.RRN = recordnumber
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-11-18 15:12:02
select * from
(select * from ordered order by order_id limit 100) x order by
x.order_id desc limit 1;
Primero seleccione las 100 filas superiores ordenando en ascendente y luego seleccione la última fila ordenando en descendente y limite a 1. Sin embargo, esta es una declaración muy costosa, ya que accede a los datos dos veces.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-06-02 20:26:39
Me parece que, para ser eficiente, necesita 1) generar un número aleatorio entre 0 y uno menos que el número de registros de la base de datos, y 2) ser capaz de seleccionar la fila en esa posición. Desafortunadamente, diferentes bases de datos tienen diferentes generadores de números aleatorios y diferentes formas de seleccionar una fila en una posición en un conjunto de resultados; por lo general, se especifica cuántas filas se omiten y cuántas filas se desea, pero se hace de manera diferente para diferentes bases de datos. Aquí hay algo que funciona para mí en SQLite:
select *
from Table
limit abs(random()) % (select count(*) from Words), 1;
Depende de poder usar una subconsulta en la cláusula limit (que en SQLite es LIMIT
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-07-17 07:46:08
En Oracle 12c, puede usar la opción OFFSET..FETCH..ROWS
con ORDER BY
Por ejemplo, para obtener el 3er registro desde arriba:
SELECT *
FROM sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-02-05 12:43:42
Para SQL server, lo siguiente devolverá la primera fila de la tabla que da.
declare @rowNumber int = 1;
select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
select TOP(@rowNumber - 1) * from [dbo].[someTable];
Puede recorrer los valores con algo como esto:
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-06-21 15:45:35