SQL DENSE_RANK Function

The SQL DENSE_RANK Function is one of the Ranking Function. The Sql Server DENSE_RANK function will assign the rank number to each record present in a partition without skipping the rank numbers.

If the SQL DENSE_RANK function encounters two equal values in the same partition, it will assign the same rank number to both values. In this article, we will show you, How to write DENSE_RANK Function in SQL Server with an example.

SQL DENSE_RANK Syntax

The syntax of the SQL Server DENSE_RANK Function is:

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

Partition_By_Clause: This will divide the records into partitions.

  • If you specified the Partition By Clause, the DENSE_RANK Function would assign the rank number to each partition.
  • If you haven’t specified, the DENSE_RANK Function will consider all the records as a single partition.

For this SQL Server DENSE_RANK function Query, We use the Rank Table data

SQL DENSE_RANK Function Example

In this SQL DENSE_RANK Function example, we show how to dense rank the partitioned records present in a table. The following Ranking Function Query will partition the data by Occupation and assigns a dense rank number using the yearly income.

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

The below SQL Server statement divides the selected data into partition using their Occupation. From the above you can observe, We got four partitions

PARTITION BY [Occupation]

In the below statement, we used the DENSE_RANK Function with Partition by clause. So, the SQL Server DENSE_RANK function will assign a dense rank number for each individual partition.

DENSE_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 assigned the 2nd rank to the next record (it won’t skip any rank numbers).

SQL DENSE_RANK without Partition By Clause

In this example, we show what will happen if we miss the Partition By Clause in DENSE_RANK SQL. For instance, the following Select Statement will use the above example without Partition by clause.

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

Within this dense_rank example, the below statement will sort the yearly income data in the descending order

ORDER BY [YearlyIncome] DESC

In the below statement, we used the DENSE_RANK Function without Partition by clause. So, the DENSE_RANK function will consider them as a single partition and assign the rank numbers from beginning to end.

DENSE_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 (80000). Next, it has given the same rank to 5 and 6 records because their yearly income is the same, and so on.

Dense_Rank Function On String Column

The SQL Server DENSE_RANK Function also allows you to rank the string columns. In this example, We are going to use the DENSE_RANK function to assign the rank numbers for [First name]

USE [SQL Server Tutorials]
GO

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

If you observe the below screenshot, the DENSE_RANK Function has given the same rank to 6 and 7 records because their First name is the same. Next, it assigned the 2nd rank to the next record.

SQL DENSE_RANK FUNCTION 3

Comments are closed.