SQL Server Row_Number Function With PARTITION BY

The row number function is one of the window functions used for assigning the row number to the rows in each of the partitions in the order in which they were sorted in the partition using the PARTITION clause, PARTITION only the ORDER clause can be used inside the OVER clause in such case the whole table will be considered as one partition. But the ORDER BY clause is mandatory for using the ROW_NUMBER() function since it arranges the rows in the partitions in that logical order and later ROW_NUMBER() function can assign the row number. In each partition, the row number starts from 1.

Syntax:

ROW_NUMBER ( )

OVER ( [ PARTITION BY col_1,col_2… ] ORDER BY col_3,col_4.. ASC or DESC) AS column_name

Components of ROW_NUMBER() function

• PARTITION BY: This is the main sub-clause that partitions the rows into windows and for each row, the values of window functions applied will be calculated.
• ORDER BY: This is used to order the rows in the partition, by default it is the ascending order. Without the ORDER BY clause, the ROW_NUMBER() function doesn’t work.
• Return type: The return type of the ROW_NUMBER() function is BIGINT.

Usage of the ROW_NUMBER() Function

Step 1: Create the database Geeksforgeeks by using the following SQL query:

Query

CREATE DATABASE Geeksforgeeks

Use the GFG Database.

Query

USE GeeksforGeeks

Step 3: Create a table with the students of different sections with their total marks out of 500.

Query

CREATE TABLE studentsSectionWise(
studentId INT,
studentName VARCHAR(100),
sectionName VARCHAR(50),
studentMarks INT 
);

Step 4: Insert the rows into the table :

Query

INSERT INTO studentsSectionWise
VALUES (1, 'Geek1','A',461),
(1, 'Geek2','B',401),
(1, 'Geek3','C',340),
(2, 'Geek4','A',446),
(2, 'Geek5','B',361),
(2, 'Geek6','C',495),
(3, 'Geek7','A',436),
(3, 'Geek8','B',367),
(3, 'Geek9','C',498),
(4, 'Geek10','A',206),
(4, 'Geek11','B',365),
(4, 'Geek12','C',485),
(5, 'Geek13','A',446),
(5, 'Geek14','B',368),
 (5, 'Geek15','C',295),
 (6, 'Geek16','C',495);

Step 5: Check the table

SELECT * FROM studentsSectionWise

Output:

studentsSectionWise table

Using ORDER_BY Clause with ROW_NUMBER() Function

Using simply the ORDER BY clause with ascending or descending considers the whole table as one partition only.

Let’s check the rankNumber of the students using the ROW_NUMBER() function with the ORDER BY clause including all sections:

SELECT * , ROW_NUMBER() OVER ( ORDER BY studentMarks DESC) AS rankNumber
FROM studentsSectionWise

Explanation: In the query we have used ORDER BY clause with the studentsMarks which is ordered in DESC order.

Output:

Output

Explanation: In the result we can see that whole table is considered as a single partition and the rankNumber number is in the increasing order with the studentsMarks in the descending order.

Using PARTITION_BY with ROW_NUMBER() Function

Using simply the PARTITION BY clause divdies and the table into different partitions based on the column name and then ORDER BY clause with ascending or descending considers the whole table as one partition only. And then the ROW_NUMBER() functions each unique number starting from 1 in each partition.

Let’s check the ranking section wise using the ROW_NUMBER() function with the PARTITION BY and ORDER BY clauses.

SELECT * , ROW_NUMBER() OVER (PARTITION BY sectionName ORDER BY studentMarks DESC) AS rankNumber
FROM studentsSectionWise;

Explanation: In the query we have used PARTITION BY clause to partition the table on the sectionName wise and ORDER BY clause with the studentsMarks which is ordered in DESC.

Output:

Output

Explanation: In the result we can see we have partitions divided by the sectionName wise and then in each partition the students are ordered on the basis of studentmarks in the DESC order , ROW_NUMBER() function assigned the row number starting from in each partition with it getting incremented.

Understanding ROW_NUMBER() Without PARTITION BY

SQL Server’s ROW_NUMBER() function is a flexible tool that allows you to provide each row in a result set a unique row number. It is equally effective when used without the PARTITION BY clause, even though it is frequently used in conjunction with it for grouping and ranking within partitions. The possibilities and uses of ROW_NUMBER() without the PARTITION BY clause will be discussed in this article.

Syntax:

SELECT

ROW_NUMBER() OVER (ORDER BY YourOrderByColumn) AS RowNum,

OtherColumns

FROM

YourTableName;

Let’s check the ranking section wise using the ROW_NUMBER() function without the PARTITION BY and ORDER BY clauses.

SELECT * , ROW_NUMBER() OVER (ORDER BY studentMarks DESC) AS rankNumber
FROM studentsSectionWise

Output:

Output

Using ROW_NUMBER() to Get the Subset of Rows

The ROW_NUMBER() function can be used to get the subset of rows from the table using the CTE which can be useful in the case of pagination of the data.

Let’s check the top 2 rankers of every class using the CTE(Common Table Expression) with ROW_NUMBER() function :

WITH topTwoRankers AS  
(  
  SELECT * , ROW_NUMBER() OVER (PARTITION BY sectionName ORDER BY studentMarks DESC) AS rankNumber
  FROM studentsSectionWise   
)   
SELECT * 
FROM topTwoRankers  
WHERE rankNumber <=2;  

Explanation: Here in the query we have used the CTE expression to get the temporary table from the query used above and the we have used the outer SELCT query to get the students whose rankNumber is less than or equal to 2.

Output:

Output

Explanation: In the result we can see we have the top 2 rankers from each section which are the subset of the table obtained from the query shown in the previous example.

Advantages of ROW_NUMBER() With PARTITION BY

1. Allows for advanced partition ranking.
2. Enables unique row numbering within specific groups.
3. Allows for greater flexibility in circumstances involving extensive analysis.

Advantages of ROW_NUMBER() Without PARTITION BY

1. Straightforward and simple.
2. makes it simple to rank items according to a particular column.
3. May offer better performance, especially for smaller result sets.

Conclusion

It is very useful when we want to have the sequencing on the basis of partition and the do the necessary operations. Using ORDER BY clause is mandatory while using the ROW_NUMBER() function. Also using the CTE we can get the subsets of the table from the ordered partitions. The main usecase of the ROW_NUMBER() being ranking of the rows on the basis of different partitions which is very useful for the data analysis purposes.