Cómo utilizar la función ROW_NUMBER en SQL Server

ROW_NUMBER, función analítica y de clasificación en SQL Server. Asigna un número de secuencia a las filas de la tabla al incrementar los valores enteros a partir de 1 para la primera fila. Los números de fila asignados actúan como valor temporal para el conjunto de resultados no persistente. La función ROW_NUMBER trabaja con la cláusula ORDER BY para ordenar las filas en el orden definido y luego numera el conjunto de resultados de la consulta.

Por ejemplo, tenemos 10 filas es una tabla, y usando Función ROW_NUMBER con cláusula ORDER BY asigna números que comienzan en 1 y terminan en 10.

Igualmente, La función ROW_NUMBER también se usa junto con la cláusula PARTITION BY para crear una ventana separada de filas según las condiciones. Por ejemplo, tenemos 10 filas en la tabla Persona, 5 hombres y 6 mujeres. El uso de la función ROW_NUMBER y la columna PARTICIÓN POR Género asignará de 1 a 5 números a los hombres y de 1 a 6 números a la partición de las mujeres.

Sintaxis

ROW_NUMBER () OVER(PARTITION BY column_name ORDER BY column_name)

Argumentos

PARTITION BY - Optional, seperate rows into different partitions and applies ROW_NUMBER function to each partition.ORDER BY - Sorts the row in a defined order and assigns number to query result set.

Función ROW_NUMBER: puntos para reflexionar

1.Asigna valores enteros crecientes a partir de 1.

2.Las filas posteriores obtendrán el siguiente valor más alto, idealmente el valor del número de fila anterior + 1.

3.Los números asignados son temporales, no persistentes; para los números persistentes, utilice los objetos IDENTITY y SEQUENCE.

4.La cláusula PARTITION BY se utiliza para crear una ventana de filas que es opcional.

5.Se pueden usar varias columnas con ROW_NUMBER y PARTITION BY para crear ventanas y asignar números a cada ventana comenzando en 1.

6.No se permiten parámetros con ROW_NUMBER.

Función ROW_NUMBER: ejemplos

Demostremos la función ROW_NUMBER con ejemplos prácticos y útiles.

1.

ROW_NUMBER - Con cláusula ORDER BY

La siguiente consulta usa la función ROW_NUMBER con la cláusula ORDER BY en la columna ID. RowNum, un nombre de columna derivado, un alias para la salida de la función ROW_NUMBER. Ordenar las filas por columna de ID y asignar un número a cada fila comenzando con 1 y aumentando el valor de las filas posteriores.

--ROW_NUMBER - With ORDER BY Clause
USE tempdb;
GO

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL 
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
  ID		INT	    PRIMARY KEY
 ,Name		VARCHAR(50)
 ,Gender	VARCHAR(6)
);

INSERT INTO dbo.Person
VALUES
 (101,'Falak'   ,'Female')
,(102,'Vishal'  ,'Male')
,(103,'Avni'    ,'Female')
,(104,'Akanksha','Female')
,(105,'Atul'	,'Male')
,(106,'Ravi'	,'Male')
,(107,'Niraj'	,'Male')
,(108,'Chetan'  ,'Male')
,(109,'Ranjana' ,'Male')
,(110,'Heena'   ,'Female');

--RowNumber with ORDER BY Clause
SELECT Id, Name, Gender, ROW_NUMBER() OVER(ORDER BY Id) As RowNum
FROM Person;

--ROW_NUMBER - With Partition By & ORDER BY Clause
USE tempdb;
GO

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL 
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
  ID		INT	      PRIMARY KEY
 ,Name		VARCHAR(50)
 ,Gender	VARCHAR(6)
);

INSERT INTO dbo.Person
VALUES
 (101,'Falak'   ,'Female')
,(102,'Vishal'  ,'Male')
,(103,'Avni'    ,'Female')
,(104,'Akanksha','Female')
,(105,'Atul'	,'Male')
,(106,'Ravi'	,'Male')
,(107,'Niraj'	,'Male')
,(108,'Chetan'  ,'Male')
,(109,'Ranjana' ,'Male')
,(110,'Heena'   ,'Female');

--RowNumber with Partition By Clause
SELECT Id, Name, Gender, 
       ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY Id) As RowNum
FROM   Person;

--ROW_NUMBER - With Multiple Columns Partition By
USE tempdb;
GO

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL 
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
  ID		INT			 PRIMARY KEY
 ,Name		VARCHAR(50)
 ,Gender	VARCHAR(6)
 ,Dept		VARCHAR(10)
);

INSERT INTO dbo.Person
VALUES
 (101,'Falak'   ,'Female','IT')
,(102,'Vishal'  ,'Male'  ,'HR')
,(103,'Avni'    ,'Female','Admin')
,(104,'Akanksha','Female','HR')
,(105,'Atul'	,'Male'  ,'Admin')
,(106,'Ravi'	,'Male'  ,'Admin')
,(107,'Niraj'	,'Male'  ,'IT')
,(108,'Chetan'  ,'Male'  ,'HR')
,(109,'Ranjana' ,'Male'  ,'Admin')
,(110,'Heena'   ,'Female','IT');

--RowNumber with Multiple Columns Partition By Clause
SELECT Id, Name, Gender, Dept,
       ROW_NUMBER() OVER(PARTITION BY Gender, Dept ORDER BY Gender, Dept) As RowNum
FROM   Person
ORDER BY Gender, Dept;

--ROW_NUMBER - With ORDER BY Clause
USE tempdb;
GO

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL 
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
  ID		INT			 
 ,Name		VARCHAR(50)
 ,Gender	VARCHAR(6)
);

INSERT INTO dbo.Person
VALUES
 (101,'Falak'   ,'Female')
,(102,'Vishal'  ,'Male')
,(101,'Falak'   ,'Female')
,(102,'Vishal'  ,'Male')
,(102,'Vishal'  ,'Male')
,(106,'Ravi'	,'Male')
,(107,'Niraj'	,'Male')
,(106,'Ravi'	,'Male')
,(109,'Ranjana' ,'Male')
,(107,'Niraj'	,'Male')
,(106,'Ravi'	,'Male')
,(110,'Heena'   ,'Female');

--With Duplicates
SELECT  FROM dbo.Person ORDER BY Id;

--Applying ROW_NUMBER and Partition by and Number Duplicates
SELECT   Id, Name, 
         Gender, 
         ROW_NUMBER() OVER(PARTITION BY Id, Name, Gender ORDER BY Id) as RowNum
FROM     dbo.Person;


--Removing Duplicates with the use CTE/ RowNumber
With DuplicateCTE AS
(
SELECT   Id, Name, 
         Gender, 
		 ROW_NUMBER() OVER(PARTITION BY Id, Name, Gender ORDER BY Id) as RowNum
FROM     dbo.Person 
)

SELECT Id, Name, Gender
FROM   DuplicateCTE
WHERE  RowNum = 1;