registros - row_number sql server ejemplos

Desplazamiento de fila en SQL Server

Question

¿Hay alguna forma en SQL Server para obtener los resultados comenzando en un desplazamiento dado? Por ejemplo, en otro tipo de base de datos SQL, es posible hacer:

SELECT * FROM MyTable OFFSET 50 LIMIT 25

para obtener resultados 51-75. Esta construcción no parece existir en SQL Server.

¿Cómo puedo lograr esto sin cargar todas las filas que no me interesan? ¡Gracias!

Answer 1

A continuación, se mostrarán 25 registros con exclusión de los primeros 50 registros que funcionan en SQL Server 2012.

SELECT * FROM MyTable ORDER BY ID OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

puede reemplazar ID como su requisito

Answer 2

Debe tener cuidado al usar la instrucción row_number () OVER (ORDER BY) ya que el rendimiento es bastante pobre. Lo mismo ocurre con el uso de Common Table Expressions con row_number () que es aún peor. Estoy usando el siguiente fragmento que ha demostrado ser un poco más rápido que usar una variable de tabla con una identidad para proporcionar el número de página.

DECLARE @Offset INT = 120000
DECLARE @Limit INT = 10
DECLARE @ROWCOUNT INT = @Offset+@Limit
SET ROWCOUNT @ROWCOUNT
SELECT * FROM MyTable INTO #ResultSet
WHERE MyTable.Type = 1
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(ORDER BY SortConst ASC) As RowNumber FROM
(
SELECT *, 1 As SortConst FROM #ResultSet
) AS ResultSet
) AS Page
WHERE RowNumber BETWEEN @Offset AND @ROWCOUNT
DROP TABLE #ResultSet

Answer 3

Dependiendo de su versión, no puede hacerlo directamente, pero podría hacer algo como hacky

select top 25 *
from (
select top 75 *
from table
order by field asc
) a
order by field desc

donde ''campo'' es la clave.

Answer 4

En SqlServer2005 puede hacer lo siguiente:

DECLARE @Limit INT
DECLARE @Offset INT
SET @Offset = 120000
SET @Limit = 10
SELECT
*
FROM
(
SELECT
row_number()
OVER
(ORDER BY column) AS rownum, column2, column3, .... columnX
FROM
table
) AS A
WHERE
A.rownum BETWEEN (@Offset) AND (@Offset + @Limit-1)

Answer 5

Esta es una forma (SQL2000)

SELECT * FROM
(
SELECT TOP (@pageSize) * FROM
(
SELECT TOP (@pageNumber * @pageSize) *
FROM tableName
ORDER BY columnName ASC
) AS t1
ORDER BY columnName DESC
) AS t2
ORDER BY columnName ASC

y esta es otra forma (SQL 2005)

;WITH results AS (
SELECT
rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
, *
FROM tableName
)
SELECT *
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

Answer 6

Evitaría usar SELECT * . Especifique las columnas que realmente quiere aunque puedan ser todas.

SQL Server 2005+

SELECT col1, col2
FROM (
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

SQL Server 2000

Navegación eficiente a través de grandes conjuntos de resultados en SQL Server 2000

Un método más eficiente para paginación a través de grandes conjuntos de resultados

Answer 7

Hay OFFSET .. FETCH en SQL Server 2012, pero deberá especificar una columna ORDER BY .

Si realmente no tiene una columna explícita que pueda pasar como una columna ORDER BY (como otros han sugerido), puede usar este truco:

SELECT * FROM MyTable
ORDER BY @@VERSION
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

... o

SELECT * FROM MyTable
ORDER BY (SELECT 0)
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

Lo estamos usando en jOOQ cuando los usuarios no especifican explícitamente un pedido. Esto producirá pedidos bastante aleatorios sin ningún costo adicional.

Answer 8

He estado buscando esta respuesta por un tiempo (para consultas genéricas) y descubrí otra forma de hacerlo en SQL Server 2000+ usando ROWCOUNT y cursores y sin TOP ni ninguna tabla temporal.

Usando el SET ROWCOUNT [OFFSET+LIMIT] puede limitar los resultados, y con los cursores, vaya directamente a la fila que desea, luego repita el ciclo hasta el final.

Entonces su consulta sería así:

SET ROWCOUNT 75 -- (50 + 25)
DECLARE MyCursor SCROLL CURSOR FOR SELECT * FROM pessoas
OPEN MyCursor
FETCH ABSOLUTE 50 FROM MyCursor -- OFFSET
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH next FROM MyCursor
END
CLOSE MyCursor
DEALLOCATE MyCursor
SET ROWCOUNT 0

Answer 9

La mejor forma de hacerlo sin perder tiempo en pedir registros es la siguiente:

select 0 as tmp,Column1 from Table1 Order by tmp OFFSET 5000000 ROWS FETCH NEXT 50 ROWS ONLY

¡lleva menos de un segundo!
La mejor solución para mesas grandes.

Answer 10

Para tablas con más y grandes columnas de datos, prefiero:

SELECT
tablename.col1,
tablename.col2,
tablename.col3,
...
FROM
(
(
SELECT
col1
FROM
(
SELECT col1, ROW_NUMBER() OVER (ORDER BY col1 ASC) AS RowNum
FROM tablename
WHERE ([CONDITION])
)
AS T1 WHERE T1.RowNum BETWEEN [OFFSET] AND [OFFSET + LIMIT]
)
AS T2 INNER JOIN tablename ON T2.col1=tablename.col1
);

-

[CONDITION] can contain any WHERE clause for searching.
[OFFSET] specifies the start,
[LIMIT] the maximum results.

Tiene un rendimiento mucho mejor en tablas con datos grandes como BLOB, porque la función ROW_NUMBER solo tiene que mirar a través de una columna, y solo se devuelven las filas correspondientes con todas las columnas.

Answer 11

Puede usar la función ROW_NUMBER() para obtener lo que desea:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM tbl) t
WHERE RowNr BETWEEN 10 AND 20

Answer 12

Si va a procesar todas las páginas en orden, simplemente recuerde el último valor clave visto en la página anterior y use TOP (25) ... WHERE Key > @last_key ORDER BY Key puede ser el método de mejor rendimiento si existen índices adecuados para permitir esto debe buscarse de manera eficiente, o un cursor API si no lo hacen.

Para seleccionar una página arbitraria, la mejor solución para SQL Server 2005 - 2008 R2 es probablemente ROW_NUMBER y BETWEEN

Para SQL Server 2012+, puede usar la cláusula ORDER BY mejorada para esta necesidad.

SELECT *
FROM MyTable
ORDER BY OrderingColumn ASC
OFFSET 50 ROWS
FETCH NEXT 25 ROWS ONLY

Aunque queda por ver qué tan bien será esta opción .

Answer 13

Ver mi selección para paginador

SELECT TOP @limit * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY colunx ASC) offset, * FROM (
-- YOU SELECT HERE
SELECT * FROM mytable
) myquery
) paginator
WHERE offset > @offset

Esto resuelve la paginación;)

Answer 14

Yo uso esta técnica para la paginación. No busco todas las filas. Por ejemplo, si mi página necesita mostrar las 100 filas superiores, solo obtengo la cláusula 100 with where. El resultado del SQL debe tener una clave única.

La tabla tiene lo siguiente:

ID, KeyId, Rank

Se asignará el mismo rango para más de un KeyId.

SQL select top 2 * from Table1 where Rank >= @Rank and ID > @Id

Por primera vez, paso 0 para ambos. La segunda vez pase 1 y 14. 3ª vez pase 2 y 6 ...

El valor del décimo registro Rank & Id se pasa al siguiente

11 21 1
14 22 1
7 11 1
6 19 2
12 31 2
13 18 2

Esto tendrá el menor estrés en el sistema

Answer 15

SELECT TOP 75 * FROM MyTable
EXCEPT
SELECT TOP 50 * FROM MyTable