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 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, SQL 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 SQL 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 SQL 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 row number example shows what happens if we miss the Partition By in SQL ROW_NUMBER Function. For example, the below query uses the above 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 the Sql Server row_number Function without Partition by clause to get the row number. 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]

ROW_NUMBER on String Column

It also allows you to rank the string columns. In this example, We are going to use the SQL 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.

Categories SQL

Comments are closed.