sentencias - seleccionar varios campos de una tabla en mysql

¿Cómo seleccionar la fila nth en una tabla de base de datos SQL?

Question

Estoy interesado en aprender algunas maneras (idealmente) independientes de la base de datos de seleccionar la fila n 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:

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

Actualmente estoy haciendo algo como lo siguiente en SQL Server 2005, pero estaría interesado en ver otros enfoques más agnósticos:

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

Crédito por el SQL anterior: Weblog de Firoz Ansari

Actualización: Vea la respuesta de Troels Arvin con respecto al estándar SQL. Troels, ¿tienes algún enlace que podamos citar?

Answer 1

SERVIDOR SQL

Seleccione n ''th registro de arriba

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 la parte inferior

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

Answer 2

1 pequeño cambio: n-1 en lugar de n.

select *
from thetable
limit n-1, 1

Answer 3

AÑADIR:

LIMIT n,1

Eso limitará los resultados a un resultado que comienza en el resultado n.

Answer 4

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 segunda ú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, puede utilizar la función CAST en MySQL.

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

Aquí, al llenar N = 4, podrá obtener el quinto último registro del monto más alto de la tabla CARTA. Puede ajustar el campo y el nombre de la tabla y proponer una solución.

Answer 5

Aquí hay una versión genérica de un sproc que escribí recientemente para Oracle que permite la paginación / clasificación 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);

Answer 6

Así es como lo haría dentro de DB2 SQL, creo que el RR / (número de registro relativo) está almacenado 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

Answer 7

Contrariamente a lo que afirman algunas de las respuestas, el estándar SQL no guarda silencio con respecto a este tema.

Desde SQL: 2003, ha podido usar "funciones de ventana" para omitir filas y limitar los conjuntos de resultados.

Y en SQL: 2008, se había agregado un enfoque un poco 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, por lo que si fuera ISO, lo habría mantenido fuera de un estándar ya bastante grande.

Answer 8

Cuando solíamos trabajar en MSSQL 2000, hicimos lo que llamamos "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ó.

Answer 9

En Oracle 12c, puede usar la opción OFFSET..FETCH..ROWS con ORDER BY

Por ejemplo, para obtener el tercer registro de arriba:

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

Answer 10

En Sybase SQL Anywhere:

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

No olvides el ORDEN POR o no tiene sentido.

Answer 11

Hay formas de hacer esto en partes opcionales del estándar, pero muchas bases de datos admiten su propia forma de hacerlo.

Un sitio realmente bueno que habla sobre esto y otras cosas es http://troels.arvin.dk/db/rdbms/#select-limit .

Básicamente, PostgreSQL y MySQL son compatibles con lo no estándar:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 y MSSQL son compatibles con las funciones de ventanas 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 vinculado anteriormente porque nunca uso esos DB)

Actualización: a partir de PostgreSQL 8.4, las funciones de ventanas estándar son compatibles, así que espere que el segundo ejemplo funcione también para PostgreSQL.

Answer 12

LÍMITE n, 1 no funciona en MS SQL Server. Creo que se trata de la única base de datos importante que no admite esa sintaxis. Para ser justos, no es parte del estándar SQL, aunque es tan ampliamente soportado que debería serlo. En todo, excepto el servidor SQL, LIMIT funciona muy bien. Para el servidor SQL, no he podido encontrar una solución elegante.

Answer 13

La sintaxis de LIMIT / OFFSET en PostgreSQL es:

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

Este ejemplo selecciona la fila 21 OFFSET 20 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 recibirá, lo que rara vez es útil.

Al parecer, el estándar SQL no dice nada sobre el problema del límite fuera de las funciones de ventanas locas, por lo que todo el mundo lo implementa de manera diferente.

Answer 14

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) poder seleccionar la fila en esa posición. Desafortunadamente, las diferentes bases de datos tienen diferentes generadores de números aleatorios y diferentes maneras de seleccionar una fila en una posición en un conjunto de resultados; por lo general, especifica cuántas filas omitir y cuántas filas desea, pero se hace de manera diferente para diferentes bases de datos. Aquí hay algo que me funciona en SQLite:

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

Depende de poder usar una subconsulta en la cláusula límite (que en SQLite es LIMIT <recs to skip>, <recs to take>) La selección del número de registros en una tabla debe ser particularmente eficiente, siendo parte de la base de datos metadatos, pero eso depende de la implementación de la base de datos. Además, no sé si la consulta realmente compilará el conjunto de resultados antes de recuperar el registro Nth, pero espero que no sea necesario. Tenga en cuenta que no estoy especificando una cláusula "ordenar por". Podría ser mejor "ordenar por" algo así como la clave principal, que tendrá un índice: obtener el registro enésimo de un índice podría ser más rápido si la base de datos no puede obtener el registro enésimo de la misma base de datos sin construir el conjunto de resultados .

Answer 15

Nada especial, no hay funciones especiales, 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 de ID o una columna de marca de datos, puede confiar.

Answer 16

No estoy seguro de nada 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 de código toma la 15ª entrada de the_table, ordenada por la fecha / hora en que se agregó:

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

(por supuesto, deberías tener un campo DATETIME agregado, y configurarlo en la fecha / hora en que se agregó la entrada ...)

Answer 17

Oráculo:

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

Answer 18

Para SQL Server, una forma genérica de ir por el número de fila es la siguiente: SET ROWCOUNT @row - @ row = el número de fila en el que desea trabajar.

Por ejemplo:

establecer el conteo de filas 20 - establece la fila en la fila 20

seleccione carne, queso de dbo.sandwich - seleccione columnas de la tabla en la fila 20

establecer el recuento de filas 0: restablece el recuento de filas en todas las filas

Esto devolverá la información de la fila 20. Asegúrese de poner en el recuento de filas 0 después.

Sé que noobish, pero soy un noob SQL y lo he usado, ¿qué puedo decir?

Answer 19

Para el servidor SQL, lo siguiente devolverá la primera fila de la tabla de resultados.

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

Puedes 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;

Answer 20

Pero realmente, ¿no es todo esto simplemente trucos de sala para un buen diseño de base de datos en primer lugar? Las pocas veces que necesité una funcionalidad como esta fue para hacer una consulta simple para hacer un informe rápido. Para cualquier trabajo real, el uso de trucos como estos está invitando a problemas. Si se necesita seleccionar una fila en particular, simplemente tenga una columna con un valor secuencial y termine con ella.

Answer 21

Por ejemplo, si desea seleccionar cada décima fila 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.

Answer 22

SQL 2005 y superior tiene esta característica incorporada. Utilice la función ROW_NUMBER (). Es excelente para páginas web con un estilo de navegación << Anterior y Siguiente >>:

Sintaxis:

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

Answer 23

Sospecho que esto es tremendamente ineficiente pero es un enfoque bastante simple, que funcionó en un pequeño conjunto de datos en el que lo 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 quinto artículo, cambia el segundo número superior para obtener un noveno elemento diferente

Solo servidor SQL (creo) pero debería funcionar en versiones anteriores que no admiten ROW_NUMBER ().

Answer 24

T-SQL - Seleccionando N''th RecordNumber 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 quinto registro 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

Answer 25

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!

Answer 26

increíble que puedes 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

Answer 27

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 fila Nth. 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

Answer 28

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

Answer 29

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

Primero seleccione las primeras 100 filas ordenando en forma ascendente y luego seleccione la última fila ordenando en forma descendente y límite a 1. Sin embargo, esta es una declaración muy costosa ya que accede a los datos dos veces.