SQL RANK Function

The SQL RANK function will assign the rank number to each record present in a partition. If the SQL Server RANK function encounters two equal values in the same partition, then it will assign the same number to both values. And it skips the next number.

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 SQL RANK Function will assign the numbers to each partition. If you haven’t, it will consider all the records as a single portion. So, it will assign the numbers from top to bottom.

For this SQL RANK Function example, we are going to use the below table data

Customer Table Data

SQL RANK Function Example

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

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 example output you can observe, We got four sections.

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, it will assign the numbers for each occupation.

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

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

SQL RANK Function without Partition By Clause

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

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

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

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

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

Ranking the String Columns

It also allows you to rank string columns. In this example query, we use this function to assign the numbers for the First name.

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

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

SQL RANK FUNCTION 3