SQL DENSE_RANK Function

The SQL Server DENSE_RANK Function is a ranking function that assigns a continuous rank number to each record 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.

The SQL DENSE_RANK Function is very useful when we want to display sequential rankings without skipping any numbers (no gaps). For instance, ranking employees by salary within the department, students rank by their individual score, or products by their sales. 

In this article, we will show you how to write the SQL Server DENSE_RANK Function with some real-time examples, common mistakes and fixing them, and best practices. Before the practical example, let me give an idea of how it works.

Imagine a student table with columns Name, Age, Email, and Marks. The task is to rank the students based on their marks. As you already know, if multiple students get the same highest marks, they all get the same rank, and the next student gets the second rank (without skipping).

SELECT Name, Age, Email, Marks,
	DENSE_RANK() OVER(ORDER BY Marks DESC) AS Ranks
FROM students

SQL DENSE_RANK Function Syntax

The syntax of the DENSE_RANK() Function that returns the BIGINT as the result set is:

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

PARTITION BY Clause: It is an optional argument that divides the records or rows in the result set into partitions.

  • If you specified the PARTITION BY Clause, the SQL DENSE_RANK Function would assign the rank number to each partition. Next, it reset the number for the next group.
  • It will consider all the records as a single partition if you haven’t specified them.

ORDER BY: Use this clause to specify how you want to sort (in ascending or descending) the column data. The DENSE_RANK() ranking factor works based on the order.

For this DENSE_RANK query demonstration, we use the Customers table data with ten records.

SQL DENSE_RANK Function Example

In this example, we show how to use DENSE_RANK with the PARTITION BY clause to partition the records present in a table and rank them. The following Ranking Function Query will partition the customers’ data by Occupation and assign a dense rank number using the yearly income.

SELECT [ID], [FirstName], [LastName], [Education],
[Occupation], [Sales], [YearlyIncome]      
,DENSE_RANK() OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
             ) AS DRWP
  FROM [Customer]

The SQL Server statement divides the selected data into partitions using their Occupation. From the last image, you can observe that we have four partitions.

PARTITION BY [Occupation]

In the statement below, we used the SQL DENSE_RANK Function with a PARTITION BY clause. The ORDER BY clause will sort the customers by income in descending order. Therefore, the function assigns a dense rank number to each partition based on the customer’s YearlyIncome. When the new partition begins, the DENSE_RANK number will reset to 1.

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

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

TIP: If you change the ORDER BY [YearlyIncome] DESC to ASC, the DENSE_RANK() function assigns ranking numbers 1 as the lowest income, n = highest income.

Within the DENSE_RANK() OVER clause, you can also use multiple columns in the ORDER BY to assign different ranks based on those two columns. It is very helpful when you have the same values in one column, you can use another column to further divide them to give distinct ranks.

DENSE_RANK() OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC,[Sales] DESC
             ) AS DRWP2

NOTE: We can use the WHERE clause in conjunction with the above statement to filter records based on the DENSE_RANK value. For instance, if we add WHERE DRWithPart < 2 as the last line. It displays the customers with rank 1.

SQL DENSE_RANK without a PARTITION BY Clause

In this example, we show what will happen if we miss the PARTITION BY Clause in the DENSE_RANK function. To demonstrate the same, we use the above-mentioned SELECT Statement without the PARTITION BY clause. It means the DENSE_RANK function considers the complete table as a single partition or group.

SELECT *,DENSE_RANK() 
    OVER (ORDER BY [YearlyIncome] DESC
           ) AS DRWithoutP
FROM [Customer]

In this example, the statement below sorts the yearly income data in descending order. Please refer to the last image to see the output.

ORDER BY [YearlyIncome] DESC

In the statement below, we used the SQL Server DENSE_RANK Function without a PARTITION BY clause. So, it will consider the whole table as a single portion and assign the rank numbers from beginning to end.

DENSE_RANK() OVER (
              ORDER BY [YearlyIncome] DESC
            ) AS DRWithoutP

It has given the same rank (2) 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.

DENSE_RANK On String Columns

The SQL Server DENSE_RANK Function also allows you to rank the string columns. In this example, we will  use this method to assign the rank numbers for [First name]

SELECT *,
      ,DENSE_RANK() OVER (
                         PARTITION BY [Occupation] 
                         ORDER BY [FirstName] DESC
                         ) AS  DRStr
  FROM [Customer]

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

SQL DENSE_RANK Function Example

DENSE_RANK Common Errors & Best Practices

While working with the SQL DENSE_RANK() function, you must be careful with a few things to avoid errors. The following list of best practices helps you with those errors completely or provides solutions for getting out.

  1. When you are working with the DENSE_RANK() function, you must place the ORDER BY clause. If you omit it, the ranking numbers are unpredictable, and it may write incorrect results.
  2. The ORDER BY clause decides the ranking factor. For instance product sales table, ASC means rank starts from the lowest sales. If it is DESC, the first rank goes to the highest sales.
  3. If your ideal is to rank based on groups, you must include the PARTITION BY clause.
  4. While using the PARTITION BY, please use the appropriate column name for grouping. For instance, Category, Country, Region, etc.
  5. While working with large datasets, either use the WHERE clause to filter the data or use CTE to get the required portion of the table.
  6. Whenever possible, use the index columns within the ORDER BY and PARTITION clauses.
  7. You can combine the SQL Server DENSE_RANK() function with other ranking functions, such as the RANK(), ROW_NUMBER, and NTILE.
  8. We can use the DENSE_RANK() function with the aggregate functions, such as SUM(), AVG(), MIN(), etc, to find the total, average, minimum, etc, within the partition.
  9. The DENSE_RANK() function treats all NULL values as identical and assigns the same rank number to all the NULL values.
Categories SQL

Comments are closed.