SQL Server Row Number

Row number is the most common ranking function used in SQL Server. The ROW_NUMBER() function generates a sequential number for each row within a partition in the resultant output. In each partition, the first-row number begins with 1. We must always use the ORDER BY clause to ensure that the numbers are assigned in the proper sequence. The values returned by this function are of BIG INT data type. This function comes with SQL Server 2005 and later MS SQL versions.

The ROW_NUMBER determines the temporary value when the query is executed. If you want to get the numbers in a table, you need to see the IDENTITY property and SEQUENCE. When the ROW_NUMBER function in SQL Server encounters two identical values in the same partition, it assigns different rank numbers to them. The rank number will be determined by the order in which they are displayed.

There is no guarantee that the value returned by this function will be ordered the same for each execution unless the following are true:

Partitioned columns values are unique
ORDER BY columns values are unique
Combination of Partition and Order By columns are unique

Syntax

The following are the syntax that illustrates the ROW_NUMBER() function:

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Let us understand the ROW NUMBER() function's syntax:

OVER

It's important to understand this clause that specifies the window or set of rows that the window function operates. The PARTITION BY and ORDER BY are the two possible clauses of the OVER clause. The OVER clause's ORDER BY expression is supported when the rows must come in a specific order for the function to execute.

PARTITION BY

It is an optional clause that splits the result set into partitions (groups of rows). Then ROW NUMBER() function is applied to each partition and assigns each partition's rank number separately.
If we omit the partition by clause, the ROW_NUMBER function will treat the whole result as a single partition and provide ranking in the top to bottom order.

ORDER BY

This clause allows us to sort the rows of the result set within each partition. It is a required clause because the ROW_NUMBER() function is order-dependent.

Example

Let us understand how the ROW_NUMBER function works in the SQL Server table with an example. First, we will create a table named "Persons" using the below statement:

CREATE TABLE Persons (  
  person_name varchar(45) NOT NULL,  
  product varchar(45) DEFAULT NULL,  
  country varchar(25) DEFAULT NULL,
  price float,
  years int NOT NULL  
);

Next, we will add some records into this table using the below statement:

INSERT INTO Persons (person_name, product, country, price, years)   
VALUES ('Steffen', 'Computer', 'USA', 20000.00, 2018),   
('Joseph', 'Laptop', 'India', 35000.00, 2016),   
('Kevin', 'TV', 'USA', 15000.00, 2016),   
('Thompson', 'Mobile', 'France', 12500.00, 2017), 
('Donald', 'Laptop', 'England', 30000.00, 2018),  
('Joseph', 'Mobile', 'India', 18000.00, 2018),  
('Matthew', 'Desktop', 'France', 22000.00, 2017),   
('Antonio', 'Mouse', 'England', 1200.00, 2016);

Next, verify the data using the SELECT statement. We will get the below output:

1. Simple ROW_NUMBER() Example

The following statement displays the person's detail and adds a sequential integer number to each row by using the ROW_NUMBER():

SELECT *, ROW_NUMBER() 
OVER (ORDER BY price) AS row_num
FROM Persons; 

Here, we have not specified the PARTITION BY clause so that the ROW_NUMBER() function will treat the whole result set as a single partition. After execution of the statement, we will get the following output:

2. ROW_NUMBER() over Partitions Example

The ROW NUMBER() function is used in this example provides a sequential number to each record within a partition in a table. It always reinitialized the row number when the year changes:

SELECT person_name, product, price, years, 
ROW_NUMBER() OVER ( PARTITION BY years ORDER BY price) AS row_num
FROM Persons;

Here, we have used the PARTITION BY clause that divides the 'Persons' table into partitions based on the 'years' column. After execution, we will get the below output:

3. ROW_NUMBER() Example for Pagination

We can also use the ROW_NUMBER() function for pagination. For example, if we want to get all of a person's information in an application by pages, we'll first assign each row a sequential number using the ROW_NUMBER() function. Second, sort the rows by the requested page.

The following statement explains it more clearly:

SELECT * FROM ( SELECT ROW_NUMBER() 
OVER (ORDER BY price) AS row_num, person_name, product, price
FROM Persons) P
WHERE row_num > 3 AND row_num <= 6;

It will give the following output:

If we do not want to use the subquery, we can use the common table expression (CTE) as below statement:

WITH CTE_Person AS (
SELECT ROW_NUMBER() 
OVER (ORDER BY price) AS row_num, person_name, product, price
FROM Persons)
SELECT * FROM CTE_Person WHERE row_num > 3 AND row_num <= 6;

In this example, we have first specified the CTE that uses the ROW_NUMBER() function to assign each row in the result set sequentially. Second, the outer query returned the desired result. After execution, we will get the following output:

Conclusion

In this article, we learned how to give a sequential number to each record within a partition of a query using the ROW NUMBER() function in SQL Server.

Next TopicRename Columns in SQL Server

← prev next →