Oracle RANK

Summary: in this tutorial, you will learn how to use 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. In case the query partition cause 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, query 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:

Oracle RANK function demo

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.

products table

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:

Oracle RANK function with ORDER BY clause

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:

Oracle RANK function - top-10 most expensive products

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:

Oracle RANK function with partition by clause

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.

Was this tutorial helpful?