SQL ROW_NUMBER

The SQL ROW_NUMBER Function is one of the SQL Server Ranking function. This SQL Server row number function assigns 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 numbers to both values. Here rank number will depend upon the order they displayed.

SQL ROW_NUMBER Syntax

The syntax of the SQL Server ROW_NUMBER Function is:

-- SQL Server ROW_NUMBER Syntax
SELECT ROW_NUMBER() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]

Partition_By_Clause: Divide the records into partitions.

  • If you specified the Partition By Clause, SQL ROW_NUMBER Function will assign the rank number to each partition.
  • If you haven’t defined the Partition By, the ROW_NUMBER Function will consider all the records as a single partition. So, ROW_NUMBER will assign the rank numbers from top to bottom.

For this SQL Server ROW_NUMBER Function example, we use the Rank Table data

SQL ROW_NUMBER Function Example

The SQL ROW_NUMBER Function assigns the row number or rank number to each record present in a partition. In this Ranking function example, we show how to rank the partitioned records present in a SQL Server table.

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

USE [SQL Server Tutorials]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
             ) AS [ROW NUMBER]
  FROM [Customers]
SQL ROW_NUMBER FUNCTION 1

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

PARTITION BY [Occupation]

In the below statement, we used SQL Server ROW_NUMBER Function with Partition by clause. So, the ROW_NUMBER function will assign row numbers for each partition.

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

Although the yearly income is the same for 3 and 4 records (80000), It has given different ranks to them. Because ROW_NUMBER assigns different ranks to each record.

SQL ROW_NUMBER without Partition By Clause

This example shows what happens if we miss the Partition By in ROW_NUMBER Function. For example, the below query uses the above SQL row number query without Partition by clause.

USE [SQL Server Tutorials]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (
                     ORDER BY [YearlyIncome] DESC
              ) AS [ROW NUMBER]
  FROM [Customers]
SQL ROW_NUMBER FUNCTION 2

The below statement will sort the yearly income data in the descending order

ORDER BY [YearlyIncome] DESC

In the below statement, we used Sql Server row number Function without Partition by clause. So, the Row number function will consider them as a single partition and assign rank numbers from beginning to end.

ROW_NUMBER() OVER (
              ORDER BY [YearlyIncome] DESC
            ) AS [ROW_NUMBER]

SQL Row Number on String Column

The SQL ROW NUMBER Function also allows you to rank the string columns. In this example, We are going to use the SQL Server ROW_NUMBER function to assign the rank numbers for [First name]

USE [SQL Server Tutorials]
GO

SELECT [LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Occupation]
      ,[FirstName]
      ,ROW_NUMBER() OVER (
                    PARTITION BY [Occupation] 
                    ORDER BY [FirstName] DESC
                   ) AS [ROW NUMBER]
  FROM [Customers]
SQL ROW_NUMBER FUNCTION 3

If you observe the above screenshot, Although the first name is the same for 6 and 7 records (John), Select statement has given different row numbers to them. Because SQL Server row number assigns unique ranks to each record.

Comments are closed.