SQL ROW_NUMBER

The ROW_NUMBER Function is one of the Ranking functions. 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 numbers will depend upon the order they are displayed.

SQL Server ROW_NUMBER Syntax

The syntax of the ROW_NUMBER Function is:

SELECT ROW_NUMBER() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]

Partition_By_Clause: Divide the records into partitions.

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

For this Function example, we use the Rank Table data that we mentioned before.

SQL Server ROW_NUMBER Function Example

This 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 Server table.

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

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

Within this example query, the below statement divides the data into partitions using their Occupation. From the above, you can observe, We got four partitions.

PARTITION BY [Occupation]

In the below statement, we used ROW_NUMBER Function with Partition by clause. So, it 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 Server 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.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (
                     ORDER BY [YearlyIncome] DESC
              ) AS [ROW NUMBER]
  FROM [Customers]
SQL ROW_NUMBER FUNCTION without Partition By clause Exanple 2

The below statement will sort the yearly income data in 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]

String Column

It also allows you to rank the string columns. In this example, We are going to use the ROW_NUMBER function to assign the rank numbers for the [First name] column.

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 on String Column 3

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

Comments are closed.