How to use ORDER_BY Clause with ROW_NUMBER() Function In SQL
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.
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.