How to Assign a Successive Number to Each Row in SQL Server?

The ROW_NUMBER() function is a type of window function that could be used in SQL Server to assign a successive number to each row within the partition of a result set. The row starts with the number 1 for the first row in each partition. It is order sensitive.

Syntax:

SELECT
ROW_NUMBER() OVER 
(
[PARTITION BY partition_value]
ORDER BY sort_value [ASC | DESC] 
)
FROM tablename ;

Where:

• PARTITION BY: The PARTITION BY clause is optional, it partitions the result set into another term for groups of rows.
• ORDER BY: The ORDER BY clause is mandatory, it specifies the logical order of the rows within each partition of the result set.

For example Let us use the below query to result in the four system tables in alphabetic order and add a column with the ROW_NUMBER function, named RowNum.

SELECT  
ROW_NUMBER() OVER(ORDER BY name ASC) 
AS RowNum,
name, recovery_model_desc
FROM sys.databases  
WHERE database_id < 5;

Output:

Using the PARTITION BY clause on the recovery_model_desc:

SELECT  
ROW_NUMBER() OVER(PARTITION BY 
recovery_model_desc ORDER BY name ASC)  
AS RowNum,
name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

Output:

In the below example, we used the ROW_NUMBER() function to assign a sequential number to each name. It reinitializes the number when the city changes:

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table “geek_tab” inside the database GeeksForGeeks. This table has 3 columns namely Name, ID, and City containing the names, ID & City.

Query:

CREATE TABLE geek_tab(
Name VARCHAR(20),
ID INT,
City VARCHAR(20));

Output:

Step 4: Insert multiple rows into the “geek_tab”  table.

Query:

INSERT INTO geek_tab VALUES('Megha',12,'Delhi');
INSERT INTO geek_tab VALUES('Neha',31,'Noida');
INSERT INTO geek_tab VALUES('Komal',45,'Delhi');
INSERT INTO geek_tab VALUES('Nisha',54,'Noida');
INSERT INTO geek_tab VALUES('Hema',43,'Gurugram');
INSERT INTO geek_tab VALUES('Khushi',65,'Noida');
INSERT INTO geek_tab VALUES('Kajal',62,'Gurugram');
INSERT INTO geek_tab VALUES('Babita',48,'Delhi');
INSERT INTO geek_tab VALUES('Gita',52,'Noida');

Step 6: Display all the rows of the table.

Query:

Select * from [geek_tab] ;

Output:

Step 7: Now, we will use the ROW_NUMBER() function to assign a sequential number to each name. It reinitialize the number when the city changes:

Query:

SELECT  Name,  ID, City,
ROW_NUMBER() OVER (
   PARTITION BY City
   ORDER BY Name
) AS Row_num
FROM  [geek_tab]
ORDER BY  City;

Output: