query - seleccionar cierta cantidad de registros sql

Cómo obtener N filas comenzando desde la fila M de la tabla ordenada en T-SQL

Question

Hay una manera simple de obtener las N primeras filas de cualquier tabla:

SELECT TOP 10 * FROM MyTable ORDER BY MyColumn

¿Hay alguna forma eficiente de consultar las filas M desde la fila N?

Por ejemplo,

Id Value
1    a
2    b
3    c
4    d
5    e
6    f

Y consultas como esta

SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */

consulta 2 filas comenzando desde la fila 3d, es decir, se devuelven las 4 y 4 filas.

Answer 1

ACTUALIZACIÓN Si está utilizando SQL 2012, se agregó una nueva sintaxis para que esto sea realmente fácil. Consulte Implementación de la función de búsqueda (omisión / recepción) con esta consulta

Supongo que lo más elegante es usar la función ROW_NUMBER (disponible desde MS SQL Server 2005):

WITH NumberedMyTable AS
(
    SELECT
        Id,
        Value,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        MyTable
)
SELECT
    Id,
    Value
FROM
    NumberedMyTable
WHERE
    RowNumber BETWEEN @From AND @To

Answer 2

¿Por qué no hacer dos consultas?

select top(M+N-1) * from table into temp tmp_final with no log;
select top(N-1) * from tmp_final order by id desc;

Answer 3

A continuación se muestra una consulta simple que enumerará N filas de M + 1ª fila de la tabla. Reemplace M y N con sus números preferidos.

Select Top N B.PrimaryKeyColumn from 
    (SELECT 
        top M PrimaryKeyColumn
     FROM 
        MyTable
) A right outer join MyTable B 
on 
    A.PrimaryKeyColumn = B.PrimaryKeyColumn
where 
    A.PrimaryKeyColumn IS NULL

Por favor, hágame saber si esto es útil para su situación.

Answer 4

El problema con las sugerencias en este hilo y en otras partes de la web es que todas las soluciones propuestas se ejecutan en tiempo lineal con respecto al número de registros. Por ejemplo, considere una consulta como la siguiente.

select *
from
(
    select
        Row_Number() over (order by ClusteredIndexField) as RowNumber,
        *
    from MyTable
) as PagedTable
where RowNumber between @LowestRowNumber and @HighestRowNumber;

Al obtener la página 1, la consulta tarda 0.577 segundos. Sin embargo, al obtener la página 15.619, esta misma consulta toma más de 2 minutos y 55 segundos.

Podemos mejorar esto al crear un número de registro, tabla cruzada de índice como se muestra en la siguiente consulta. La tabla cruzada se llama PagedTable y no es persistente.

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

Como en el ejemplo anterior, probé esto en una tabla muy amplia con 780,928 registros. Usé un tamaño de página de 50, que resultó en 15,619 páginas.

El tiempo total tomado para la página 1 (la primera página) es 0.413 segundos. El tiempo total empleado para la página 15.619 (la última página) es de 0.987 segundos, simplemente el doble de la página 1. Estos tiempos se midieron utilizando SQL Server Profiler y el DBMS fue SQL Server 2008 R2.

Esta solución funciona para cualquier caso cuando clasifique su tabla por índice. El índice no tiene que ser agrupado o simple. En mi caso, el índice estaba compuesto por tres campos: varchar (50) asc, varchar (15) asc, numérico (19,0) asc. Que el rendimiento fue excelente a pesar del engorroso índice demuestra que este enfoque funciona.

Sin embargo, es fundamental que la cláusula order by en la función de ventana Row_Number corresponda a un índice. De lo contrario, el rendimiento se degradará al mismo nivel que el primer ejemplo.

Este enfoque todavía requiere una operación lineal para generar la tabla cruzada no persistente, pero dado que es solo un índice con un número de fila agregado, sucede muy rápidamente. En mi caso, me llevó 0.347 segundos, pero mi caso tenía varcharrs que necesitaban ser copiados. Un solo índice numérico tomaría mucho menos tiempo.

Para todos los propósitos prácticos, este diseño reduce la escala de la paginación del lado del servidor de una operación lineal a una operación logarítmica que permite escalar tablas grandes. A continuación está la solución completa.

-- For a sproc, make these your input parameters
declare
    @PageSize int = 50,
    @Page int = 15619;

-- For a sproc, make these your output parameters
declare @RecordCount int = (select count(*) from MyTable);
declare @PageCount int = ceiling(convert(float, @RecordCount) / @PageSize);
declare @Offset int = (@Page - 1) * @PageSize;
declare @LowestRowNumber int = @Offset;
declare @HighestRowNumber int = @Offset + @PageSize - 1;

select
    @RecordCount as RecordCount,
    @PageCount as PageCount,
    @Offset as Offset,
    @LowestRowNumber as LowestRowNumber,
    @HighestRowNumber as HighestRowNumber;

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

Answer 5

En SQL 2012 puede usar OFFSET y FETCH :

SELECT * FROM MyTable ORDER BY MyColumn OFFSET @N ROWS FETCH NEXT @M ROWS ONLY; Yo personalmente prefiero:

DECLARE @CurrentSetNumber int = 0;
DECLARE @NumRowsInSet int = 2;

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @NumRowsInSet * @CurrentSetNumber ROWS
FETCH NEXT @NumRowsInSet ROWS ONLY;

SET @CurrentSetNumber = @CurrentSetNumber + 1;

donde @NumRowsInSet es el número de filas que desea que se devuelva y @CurrentSetNumber es el número de @NumRowsInSet debe omitir.

Answer 6

Encuentre la identificación para la fila N Luego obtenga las primeras filas M que tienen una identificación mayor o igual a esa

declare @N as int set @N = 2 declare @M as int set @M = 3 declare @Nid as int set @Nid = max(id) from (select top @N * from MyTable order by id) select top @M * from MyTable where id >= @Nid order by id

Algo así ... pero he hecho algunas suposiciones aquí (por ejemplo, quieres pedir por identificación)

Answer 7

Este hilo es bastante antiguo, pero actualmente puedes hacer esto: mucho más limpio.

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

fuente: http://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/

Answer 8

Feo, hackish, pero debería funcionar:

select top(M + N - 1) * from TableName
except
select top(N - 1) * from TableName

Answer 9

Hay un método bastante directo para T-SQL , aunque no estoy seguro si es pre-efectivo si se salta una gran cantidad de filas.

SELECT TOP numberYouWantToTake 
    [yourColumns...] 
FROM yourTable 
WHERE yourIDColumn NOT IN (
    SELECT TOP numberYouWantToSkip 
        yourIDColumn 
    FROM yourTable 
    ORDER BY yourOrderColumn
)
ORDER BY yourOrderColumn

Si usa .Net, puede usar lo siguiente, por ejemplo, un IEnumerable con sus resultados de datos:

IEnumerable<yourDataType> yourSelectedData = yourDataInAnIEnumerable.Skip(nubmerYouWantToSkip).Take(numberYouWantToTake);

Esto tiene la desventaja de que está obteniendo todos los datos del almacenamiento de datos.

Answer 10

Leí todas las respuestas aquí y finalmente encontré una solución utilizable que es simple. Los problemas de rendimiento surgen de la declaración BETWEEN, no de la generación de los números de filas. Así que utilicé un algoritmo para hacer paginación dinámica pasando el número de página y el número de registros.

Los pases no son fila de inicio y número de filas, sino "filas por página (500)" y "número de página (4)", que serían las filas 1501 - 2000. Estos valores pueden reemplazarse por variables de procedimiento almacenadas para que no bloqueado en el uso de una cantidad de paginación específica.

select * from (
    select
        (((ROW_NUMBER() OVER(ORDER BY MyField) - 1) / 500) + 1) AS PageNum
        , *
    from MyTable
) as PagedTable
where PageNum = 4;

Answer 11

Para hacer esto en SQL Server, debe ordenar la consulta por una columna, para que pueda especificar las filas que desea.

No puede usar la palabra clave "TOP" al hacer esto, debe usar N filas de compensación para obtener las próximas M filas.

Ejemplo:

select * from table order by [some_column] 
offset 10 rows
FETCH NEXT 10 rows only

Puede obtener más información aquí: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx

Answer 12

Probablemente bueno para pequeños resultados, funciona en todas las versiones de TSQL:

SELECT 
        * 
FROM
     (SELECT TOP (N) * 
      FROM 
            (SELECT TOP (M + N - 1) 
             FROM 
                   Table
             ORDER BY 
                      MyColumn) qasc
      ORDER BY 
               MyColumn DESC) qdesc
 ORDER BY 
         MyColumn

Answer 13

Si desea seleccionar 100 registros del 25 ° registro:

select TOP 100 * from TableName
where PrimaryKeyField 
   NOT IN(Select TOP 24 PrimaryKeyField from TableName);

Answer 14

Y así es como puede lograr el mismo objetivo en tablas sin clave principal:

select * from
(
    select row_number() over(order by (select 0)) rowNum,*
    from your_table
) tmp
where tmp.rowNum between 20 and 30 -- any numbers you need

Answer 15

        -- *some* implementations may support this syntax (mysql?)
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 , 0
   ;

        -- Separate LIMIT, OFFSET
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 OFFSET 2
   ;

        -- SQL-2008 syntax
SELECT Id,Value
FROM xxx
ORDER BY Id
OFFSET 4
FETCH NEXT 2 ROWS ONLY
  ;

Answer 16

@start = 3
@records = 2

Select ID, Value 
From
(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNum, ID,Value 
From MyTable) as sub
Where sub.RowNum between @start and @start+@records

Esta es una forma. hay muchos otros si busca en Google SQL Paging.

Answer 17

SELECT * FROM (
  SELECT
    Row_Number() Over (Order by (Select 1)) as RawKey,
    * 
  FROM [Alzh].[dbo].[DM_THD_TRANS_FY14]
) AS foo
WHERE RawKey between 17210400 and 17210500