2014/11/26

USANDO TOP Y FILTRANDO CON OFFSET

En ocasiones requerimos filtrar cierta cantidad de filas de nuestro conjunto de datos en el cual deseamos obtener las primeras 5 , 10 o N filas de nuestro conjunto de datos o también, nos hemos preguntado como obtener un bloque de N filas después de saltar otra N cantidad de filas, bien ahora les explicaré como podremos lograr esto con TOP y con OFFSET.

TOP

Podemos extraer las primeras N cantidad de filas de un conjunto de datos, es necesario utilizar la cláusula ORDER BY. Un ejemplo es la siguiente consulta, el resultado sin la cláusula TOP es de 316 filas:
USE AdventureWorks2012
SELECT 
TOP(8) 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID

Ahora bien, también es posible extraer un porcentaje de la cantidad total de filas con la opción PERCENT, recordemos que el total de filas es de 316, si queremos extraer un 10% el resultado sería 31.6 filas, al obtener decimales el valor se redondea al entero siguiente.
SELECT 
TOP(10) PERCENT
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

Además tenemos la opción WITH TIES, con la cual es posible obtener aquellas filas que tengan el mismo valor en las columnas declaradas en la cláusula ORDER BY, pero esto solo se aplica para la última fila de nuestro conjunto de datos filtrado por el TOP. Como podemos observar en el siguiente ejemplo, la última fila se repite el valor 48.101 en 3 ocasiones.
--sin opción WITH TIES
SELECT 
TOP(9) 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

-- usando opción WITH TIES
SELECT 
TOP(9) WITH TIES
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

Es necesario subrayar que si requerimos obtener siempre los mismos resultados, necesitamos aplicar un ORDER BY donde el motor pueda identificar que son valores únicos o utilizar la opción WITH TIES, ya que si existen valores repetidos, SQL SERVER no podrá garantizar que siempre obtenga los mismos resultados cada vez que ejecutemos la consulta.

OFFSET

Es posible saltar N cantidad de filas y obtener las N filas siguientes, un ejemplo es el siguiente:
-- Sin OFFSET
SELECT 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

-- usando OFFSET
SELECT 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC
OFFSET 10 ROWS;

Y también podemos indicarle la cantidad de filas que deseamos obtener después de realizar el salto:
SELECT 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC
OFFSET 10 ROWS
FETCH NEXT 100 ROWS ONLY;

Existen también las opciones FIRST en lugar de NEXT y ROW en lugar de ROWS, aunque si usamos en la consulta previa dichas opciones, no cambiara en nada, aunque posiblemente exista una confusión al momento de leer la consulta:
SELECT 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC
OFFSET 10 ROW
FETCH FIRST 100 ROW ONLY;

Por lo tanto el cambio de estas opciones no afecta en el resultado. Con estas opciones es posible realizar la tan solicitada paginación de un conjunto de datos dentro de SQL SERVER, lo cual podremos ver un poco más adelante.

SALUDOS!
COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE

0 comentarios:

Publicar un comentario