SQL ROW_NUMBER

The SQL ROW_NUMBER Function is one of the Ranking Function in SQL Server. This Sql Server row number function will assign the sequential rank number to each unique record present in a partition.

If the Sql Server ROW_NUMBER function encounters two equal values in the same partition, it will assign the different rank number to both values. Here rank number will depend upon the order they displayed.

SQL ROW_NUMBER Syntax

Partition_By_Clause: This will divide the records selected by the SQL SELECT Statement into partitions.

If you specified the Partition By Clause then, SQL ROW_NUMBER Function will assign the rank number to each partition.

If you haven’t specified the Partition By Clause then, SQL Server ROW_NUMBER Function will consider all the records as a single partition so, it will assign the rank numbers from top to bottom.

Order_By_Clause: This is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.

In this article, we will show you, How to write ROW_NUMBER Function in SQL Server with an example. For this, We are going to use the below-shown data

SQL ROW_NUMBER Function Example

The SQL ROW_NUMBER Function allows you to assign the row number or rank number to each record present in a partition. In this example, we will show you, How to rank the partitioned records present in a table.

The following Sql row number query will partition the data by Occupation and assign the rank number using the yearly income.

Within this Sql Server Row_Number example query, Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions

PARTITION BY [Occupation]

Below statement will sort the partitioned data in the descending order using their [yearly income]

ORDER BY [YearlyIncome] DESC

In the below statement we used Sql Server ROW_NUMBER Function with Partition by clause so, ROW_NUMBER function will assigns rank numbers for each individual partition.

ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]

Although the yearly income of is same for 3 and 4 records (80000), It has given different rank to them because ROW_NUMBER() assign unique ranks to each individual record.

SQL ROW_NUMBER without Partition By Clause

In this example we will show you, What will happen if we miss the Partition By Clause in ROW_NUMBER() Function. For instance, The following query will use the above Sql row number example query without Partition by clause.

If you observe the above screenshot, Although the first name is same for 6 and 7 records (John), It has given different rank to them. Because Sql Server row number assign unique ranks to each individual record.