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

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 en gran medida mediante la creación de un número de registro, índice de la tabla cruzada 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, lo que resultó en 15,619 páginas.

El tiempo total tomado para la página 1 (la primera página) es de 0.413 segundos. El tiempo total tomado para la página 15,619 (la última página) es de 0.987 segundos, simplemente dos veces más que la página 1. Estos tiempos se midieron utilizando SQL Server Profiler y el DBMS fue SQL Servidor 2008 R2.

Esta solución funciona para cualquier caso cuando usted está ordenando su tabla por un í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. El hecho de que el rendimiento fuera excelente a pesar del engorroso índice demuestra aún más que este enfoque funciona.

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

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

Para todos los propósitos prácticos, este diseño reduce la escala del lado del servidor paginación de una operación lineal a una operación logarítmica que permite la escala de tablas grandes. A continuación se muestra 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;

En SQL 2012 puede usar OFFSET y FETCH:

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

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 devuelvan y @CurrentSetNumber es el número de @NumRowsInSet para saltar.

Si desea seleccionar 100 registros del registro 25:

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

Feo, hackish, pero debería funcionar:

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

Probablemente bueno para resultados pequeños, 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

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

@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 manera. hay muchos otros si google SQL Paging.

Este hilo es bastante antiguo, pero actualmente puedes hacer esto: mucho más limpio en mi humilde opinión

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/

A continuación, la consulta simple 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.

Y así es como se puede lograr el mismo objetivo en las tablas sin clave primaria:

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

Leí todas las respuestas aquí y finalmente se me ocurrió 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 fila en sí. Así que usé 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 inicial y número de filas, sino más bien "filas por página (500)" y "número de página (4)" que serían filas 1501 - 2000. Estos valores pueden ser reemplazados por almacenados variables de procedimiento para que no esté 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;

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 el desplazamiento N filas para obtener las siguientes 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

Buscar id para la fila N A continuación, obtener las filas superiores M que tienen un id mayor o igual que

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, desea ordenar por id)

Hay un método bastante sencillo para T-SQL, aunque no estoy seguro de si es prestanda-efectivo si estás saltando un gran número 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 está utilizando. Net, puede usar lo siguiente en, por ejemplo, unerableumerable con sus resultados de datos:

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

Esto tiene la parte trasera que usted está recibiendo todos los datos del almacenamiento de datos.

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

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