Oracle RANK

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

Introduction to Oracle RANK() function

The RANK() function is an analytic function that calculates the rank of a value in a set of values.

The RANK() function returns the same rank for the rows with the same values. It adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

The RANK() function is useful for top-N and bottom-N queries.

The following illustrates the syntax of the RANK() function:

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

The order_by_clause is required. It species the order of rows in each partition to which the RANK() function applies.

The query partition clause, if available, divides the rows into partitions to which the RANK() function applies. If the query_partition_clause is omitted, the whole result set is treated as a single partition.

Oracle RANK() function examples

First, create a new table named rank_demo that consists of one column:

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

Second, insert some rows into the rank_demo table:

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

Third, get data from the rank_demo table:

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

Fourth, use the RANK() function to calculate the rank for each row of the rank_demo table:

SELECT 
	col, 
	RANK() OVER (ORDER BY col) my_rank
FROM 
	rank_demo;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

The first two rows received the same rank 1. The third row got the rank 3 because the second row already received the rank 1. The next three rows received the same rank 4 and the last row got the rank 7.

Oracle RANK() function examples

We’ll use the products table from the sample database for demonstration.

Oracle RANK() function simple example

The following statement calculates the rank of each product by its list price:

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

Here is the partial output:

To get the top 10 most expensive products, you use the following statement:

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

In this example, the common table expression returned products with their ranks, and the outer query selected only the first 10 most expensive products.

Here is the output:

Using Oracle RANK() function with PARTITION BY example

The following example returns the top-3 most expensive products for each category:

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

In this example:

• First, the PARTITION BY clause divided the products into multiple partitions by category.
• Then, the ORDER BY clause sorted the rows in each partition by list price in descending order.
• Finally, the RANK() function calculated the rank for each row in each partition. It re-initialized the rank for each partition.

The following shows the output:

In this tutorial, you have learned how to calculate the rank of a value in a set of values by using the Oracle RANK() function.