SQL Server Row_Number Function With PARTITION BY

ROW_NUMBER() is a window function that assigns a sequential number to rows within each partition, in the order defined by the ORDER BY inside the OVER(...) clause. The PARTITION BY is optional—if you omit it, the entire result set is treated as a single partition.

The ORDER BY clause is mandatory for ROW_NUMBER() because it defines the logical order used to number rows. In each partition, numbering starts at 1.

Example: Using the ROW_NUMBER() window 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:

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.

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:

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 the PARTITION BY clause divides the table into separate partitions based on the specified column, and the ORDER BY clause arranges the rows within each partition in ascending or descending order. The ROW_NUMBER() function then assigns a unique sequential number to each row, starting from 1 in every 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:

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:

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:

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.