Oracle DENSE_RANK

Summary: in this tutorial, you will learn how to use the Oracle DENSE_RANK() function to calculate the rank of a row in an ordered set of rows.

Introduction to Oracle DENSE_RANK() function

The DENSE_RANK() is an analytic function that calculates the rank of a row in an ordered set of rows. The returned rank is an integer starting from 1.

Unlike the RANK() function, the DENSE_RANK() function returns rank values as consecutive integers. It does not skip rank in case of ties. Rows with the same values for the rank criteria will receive the same rank values.

The following shows the syntax of DENSE_RANK():

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)Code language: SQL (Structured Query Language) (sql)

In this syntax, the order_by_clause is required because the DENSE_RANK() function is ordered sensitive. The following is the syntax of the order by clause:

ORDER BY expression1 [,expression2,...] [ASC | DESC ] [NULLS FIRST | LAST]Code language: SQL (Structured Query Language) (sql)

If you omit the query_partition_by clause, the function will treat the whole result set as a single partition. Otherwise, the partition by clause will divide the result set into partitions to which the function applies.

PARTITION BY expression1 [,expression2, ...]Code language: SQL (Structured Query Language) (sql)

Note that the partition by clause must appear before the order by clause.

You will find the DENSE_RANK() function very useful for top-N and bottom-N queries.

Oracle DENSE_RANK() function examples

Let’s take a simple example to understand the DENSE_RANK() function:

Oracle DENSE_RANK() function illustration

First, create a new table named dense_rank_demo for demonstration:

CREATE TABLE dense_rank_demo (
    col VARCHAR2(10) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Next, insert some values into the dense_rank_demo table:

INSERT ALL 
    INTO dense_rank_demo(col) VALUES('A')
    INTO dense_rank_demo(col) VALUES('A')
    INTO dense_rank_demo(col) VALUES('B')
    INTO dense_rank_demo(col) VALUES('C')
    INTO dense_rank_demo(col) VALUES('C')
    INTO dense_rank_demo(col) VALUES('C')
    INTO dense_rank_demo(col) VALUES('D')
SELECT 1 FROM dual; 
Code language: SQL (Structured Query Language) (sql)

Then, query data from the dense_rank_demo table:

SELECT col FROM dense_rank_demo;Code language: SQL (Structured Query Language) (sql)

After that, use the DENSE_RANK() function to calculate a rank for each row:

SELECT
	col,
	DENSE_RANK () OVER ( 
		ORDER BY col ) 
	col
FROM
	dense_rank_demo;Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

As clearly shown in the output:

• Rows with the same values such as first and second receive the same rank values.
• Rank values are consecutive even in the event of ties.

Oracle DENSE_RANK() function examples

We’ll use the products table from the sample database to demonstrate the DENSE_RANK() function:

The following example uses the DENSE_RANK() function to calculate rank values with the list price as a rank criterion for each product:

SELECT 
    product_name, 
    list_price, 
    RANK() OVER(ORDER BY list_price) 
FROM 
    products;    
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

To get the top-10 cheapest products, you use a common table expression that wraps the above query and selects only 10 products with the lowest prices as follows:

WITH cte_products AS(  
SELECT 
    product_name, 
    list_price, 
    RANK() OVER(
    ORDER BY list_price
    ) my_rank
FROM 
    products
)
SELECT * FROM cte_products
WHERE my_rank <= 10;Code language: SQL (Structured Query Language) (sql)

Here is the output:

Oracle DENSE_RANK() function with PARTITION BY clause example

The following query returns the top five cheapest products in each category:

WITH cte_products AS(  
SELECT 
    product_name, 
    category_id,
    list_price, 
    RANK() OVER (
    PARTITION BY category_id
    ORDER BY list_price
    ) my_rank
FROM 
    products
)
SELECT * FROM cte_products
WHERE my_rank <= 5;Code language: SQL (Structured Query Language) (sql)

The following is the output:

In this example:

• First, the PARTITION BY clause distributed the rows from the products table into partitions by category id.
• Then, the ORDER BY clause sorted the rows in each partition by list price in ascending order.
• Finally, the DENSE_RANK() function applied to the rows in each partition. It re-initialized the rank values for each new partition.

In this tutorial, you have learned how to calculate ranks without gaps for a value in an ordered set of values by using the Oracle DENSE_RANK() function.