SQL RANK Function

The SQL RANK Function is one of the Ranking Function. This Sql Server rank function will assign the rank number to each record present in a partition. If the RANK function in SQL Server encounters two equal values in the same partition, then it will assign the same rank number to both values. And skips the next number in the ranking.

SQL RANK Function Syntax

The syntax of the SQL Server rank function is

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

Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions. If you specified the Partition By Clause, then the RANK Function will assign the rank number to each partition. If you haven’t, the RANK Function will consider all the records as a single partition. So, it will assign the rank numbers from top to bottom.

For this RANK Function in SQL Server example, we are going to use the Rank Table data

SQL RANK Function Example

The RANK Function in SQL Server allows you to assign the rank number to each record present in a partition. In this Ranking Function example, we will show you how to rank the partitioned records present in a SQL Server table. The following Select Statement will partition the data by Occupation and assign the rank number using the yearly income.

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

It divides the selected data into partition using their Occupation. From the above you can observe, We got four partitions

PARTITION BY [Occupation]

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

ORDER BY [YearlyIncome] DESC

We used SQL Server RANK Function with Partition by clause. So, the Rank function will assign the rank numbers for each partition.

RANK() OVER (
              PARTITION BY [Occupation] 
              ORDER BY [YearlyIncome] DESC
             ) AS RANK

It has given the same rank to 3 and 4 records because their yearly income is the same. Next, it skipped one rank and assigned the 3rd rank to the next record.

SQL RANK Function without Partition By Clause

In this rank function example, we will show you, What will happen if we miss or without the Partition By Clause in the RANK Function. For instance, it uses the above example query without Partition by clause.

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

We used RANK Function without Partition by clause. So, the Rank function considers them as a single partition and assign the rank numbers from beginning to end.

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

It has given the same rank to 2, 3, and 4 records because their yearly income is the same. Next, it skipped two ranks for those two records (3, 4) and assigned the 5th rank to the next record.

RANK Function On String Column

The RANK Function in SQL Server also allows you to rank string columns. In this rank function example, we use the RANK() function to assign the rank numbers for [First name]

USE [SQL Server Tutorials]
GO

SELECT [LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Occupation]
      ,[FirstName]
      ,RANK() OVER (
                    PARTITION BY [Occupation] 
                    ORDER BY [FirstName] DESC
                   ) AS RANK
  FROM [Customers]

The rank Function has given the same rank to 6 and 7 records because their First name is the same. Next, it skipped one rank and assigned the 3rd rank to the next record.

SQL RANK FUNCTION 3