How to use PARTITION_BY with ROW_NUMBER() Function In SQL
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:
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.
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.
Contact Us