Oracle DENSE_RANK() function

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.

The following shows the syntax of DENSE_RANK():

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

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]

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

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

);

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; 

Then, query data from the dense_rank_demo table:

SELECT col FROM dense_rank_demo;

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;

The following picture shows the output:

Oracle DENSE_RANK illustration

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:

products table

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

SELECT 

    user_code, 

    ITEM_PRICE, 

    RANK() OVER(ORDER BY ITEM_PRICE) 

FROM 

    ITEMS;

Here is the partial output:

Oracle DENSE_RANK function example

To get the top-10 cheapest product, 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;