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
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