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.

Here’s the basic 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 useful for top-N and bottom-N queries.

Basic Oracle DENSE_RANK() function example #

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)

Try it

Next, insert some values into the dense_rank_demo table:

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

Try it

Then, query data from the dense_rank_demo table:

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

Try it

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

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

Try it

Output:

COL        DENSE_RANK_VALUE
---------- ----------------
A                         1
A                         1
B                         2
C                         3
C                         3
C                         3
D                         4

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.

Using Oracle DENSE_RANK() function with table data #

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,
  DENSE_RANK() OVER (
    ORDER BY
      list_price
  )
FROM
  products;Code language: SQL (Structured Query Language) (sql)

Try it

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,
      DENSE_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)

Try it

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, 
    DENSE_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)

Try it

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.

DENSE_RANK vs. RANK #

The following table highlights the differences between DENSE_RANK and RANK1 functions:

Function	RANK()	DENSE_RANK()
Definition	Assigns a unique rank, skipping ranks after ties	Assigns a unique rank, no skipping after ties
Ranking Gaps	Yes — create gaps if there are ties	No — create no gaps; next rank is sequential
Tied Values	Tied rows receive the same rank	Tied rows receive the same rank
Next Rank After Ties	Skips ranks according to the number of ties	The next rank increases by 1
Usage	When gaps in ranking are acceptable	When consecutive ranking is needed

Summary #

• Use the DENSE_RANK() function to calculate ranks without gaps for a value in an ordered set of values.

Quiz #