Función Row_Number en SQL Server
Enumera la lista de registros resultantes en una instrucción Select.
Puede especificarse una columna como particionamiento de los resultados y debe especificarse de manera obligatoria un campo por el que se ordenarán los registros resultantes.
Sintaxis:
ROW_NUMBER ()
OVER ( [ Partition by Campo1 , … [ n ] ] Order by [Campo1, Campo2,…] )
Usando la base de datos Northwind
use Northwind
go
Ejercicio 1
Listado de los productos, ordenados por unidades en Stock descendente.
Note que se ha incluido la cláusula Order by.
select
ROW_NUMBER() over (order by P.UnitsInStock desc) As ‘Orden’,
P.ProductName,
P.QuantityPerUnit,
P.UnitPrice,
P.UnitsInStock
from dbo.Products As P
order by P.UnitsInStock desc
go
El resultado se muestra en la siguiente imagen.
Los valores de la columna Orden son mostrados usando la función Row_Number
En el ejercicio anterior, no es necesario ordenar
select
ROW_NUMBER() over (order by P.UnitsInStock desc) As 'Orden',
P.ProductName,
P.QuantityPerUnit,
P.UnitPrice,
P.UnitsInStock
from dbo.Products As P
go
Ejercicio 2
Listado de productos ordenados por unidades en stock en cada categoría.
select
ROW_NUMBER() over (partition by P.categoryid order by P.UnitsInStock desc) As 'Orden',
P.ProductName,
P.QuantityPerUnit,
P.UnitPrice,
P.UnitsInStock,
C.CategoryName
from dbo.Products As P
join dbo.Categories As C on P.CategoryID = C.CategoryID
go
El resultado se muestra en la siguiente imagen.
Note en la imagen que se ordena primero por nombre de la categoría y los productos de la misma categoría se ordenan por Stock en orden descendente. Puede verse la numeración de la función Row_Number que se reinicia en cada categoría.
Ejercicio 3
Ordenamiento por más de un campo
Listado de los productos ordenados por ID de la categoría, luego los que sean
de la misma categoría los ordena primero por unidades en Stock y luego por precio.
select
ROW_NUMBER() over (partition by P.categoryid
order by P.UnitsInStock desc, P.unitPrice desc) As 'Orden',
C.CategoryID As 'Id. Categoría',
C.CategoryName,
P.ProductName,
P.QuantityPerUnit,
P.UnitPrice,
P.UnitsInStock
from dbo.Products As P
join dbo.Categories As C on P.CategoryID = C.CategoryID
go
El resultado se muestra en la siguiente imagen.
Ejercicio 4
Empleados y cantidad de órdenes
select
ROW_NUMBER() over (order by Count(O.OrderID) desc) As 'Posición',
Empleado = E.LastName + SPACE(1) + E.FirstName,
Count(O.OrderID) As 'Cantidad de Órdenes',
Sum(O.Freight) As 'Monto total'
from Orders As O
join Employees As E on O.EmployeeID = E.EmployeeID
Group by E.LastName + SPACE(1) + E.FirstName
go
El resultado se muestra en la siguiente imagen.
Ejercicio 5
Categorías y cantidad de productos
Select
ROW_NUMBER() over (order by Count(P.CategoryID) desc) As 'Posición',
C.CategoryID As 'Código',
C.CategoryName As 'Categoría',
Count(P.CategoryID) As 'Cantidad de Productos'
from Categories As C
join Products As P on C.CategoryID = P.CategoryID
group by C.CategoryID, C.CategoryName
go
El resultado se muestra en la siguiente imagen.
Ejercicio 6
Productos y la cantidad vendida, solamente de aquellos productos que se
vendieron mas de 600 unidades de las categorías 3,5,6 y 8
select
ROW_NUMBER() over (order by C.CategoryID asc,
SUM(OD.Quantity) desc) As 'Posición',
P.ProductID As 'Cód. Producto',
P.ProductName As 'Descripción',
SUM(OD.Quantity) As 'Cantidad Vendida',
C.CategoryID As 'Cód. Categoría',
C.CategoryName As 'Nombre Categoría'
from Products As P
join [Order Details] As OD on P.ProductID = OD.ProductID
join Categories As C on P.CategoryID = C.CategoryID
where C.CategoryID in (3,5,6,8)
Group by P.ProductID, P.ProductName, C.CategoryID, C.CategoryName
Having SUM(OD.Quantity) > 600
go
El resultado se muestra en la siguiente imagen.
Ejercicio 7
El mismo listado del ejercicio anterior pero particionado por categoría
select
ROW_NUMBER() over (
Partition by C.CategoryID
order by C.CategoryID asc,
SUM(OD.Quantity) desc) As 'Posición',
P.ProductID As 'Cód. Producto',
P.ProductName As 'Descripción',
SUM(OD.Quantity) As 'Cantidad Vendida',
C.CategoryID As 'Cód. Categoría',
C.CategoryName As 'Nombre Categoría'
from Products As P
join [Order Details] As OD on P.ProductID = OD.ProductID
join Categories As C on P.CategoryID = C.CategoryID
where C.CategoryID in (3,5,6,8)
Group by P.ProductID, P.ProductName, C.CategoryID, C.CategoryName
Having SUM(OD.Quantity) > 600
go
El resultado se muestra en la siguiente imagen.
Note que primero aparece los 6 primeros productos de la categoría Confections, cuya númeración es del 1 al 6, luego los productos de la categoría Grains/Cereals cuya numeración es del 1 al 3. Luego la categoría Meat/Poultry, enumerados del 1 al 5 y al final la categoría Seafood con los valores del 1 al 6.