SQL DENSE_RANK Function

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

If the 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 Sql DENSE_RANK Function with an example.

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.
  • It will consider all the records as a single partition if you haven’t specified them.

For this query, We use the below data

Customer Table Data

SQL DENSE_RANK Function Example

In this 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.

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 partitions 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 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 numbers).

SQL Server DENSE_RANK without Partition By Clause

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

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,DENSE_RANK() OVER (
                     ORDER BY [YearlyIncome] DESC
              ) AS RANK
  FROM [Customers]
without Partition By Clause 2

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

ORDER BY [YearlyIncome] DESC

In the below statement, we used the DENSE_RANK Function without Partition by clause. So, it will consider them as a single portion 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 number to 5 and 6 records because their yearly income is the same, and so on.

On String Columns

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

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, this 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.