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

For this SQL RANK Function example, we will use the table data below.

Customer Table Data

SQL Server RANK Function Example

It allows you to assign the number to each record present in a partition. In this SQL example, we will show you how to rank rows within the partitioned rows present in a 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 partitions using their Occupation. You can observe from the above Server example output that we got four sections.

PARTITION BY [Occupation]

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

ORDER BY [YearlyIncome] DESC

We used 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 Server RANK Function without Partition By Clause

In this 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 Server RANK Function without Partition by clause. So, this function considers them as a single category and assigns 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 SQL function to assign the rank numbers for the string column 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
Categories SQL