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.

Here’s the basic syntax of the RANK() function:

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

In this syntax:

• First, specify the RANK with opening and closing parentheses.
• Second, specify the keyword OVER with opening and closing parentheses
• Third, provide the query partition clause and order by clause. The query_partition clause is optional whereas the order_by_clause is required.

The query partition clause divides the rows into partitions to which the RANK() function applies. If you omit the query_partition_clause , the function treats the whole result set as a single partition.

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

The RANK() function creates gaps in ranking values. If you don’t want to have gaps, you can use the DENSE_RANK() function. Here are the differences between RANK() and DENSE_RANK() functions.

Oracle RANK() function examples #

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

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

Try it

Second, insert some rows into the rank_demo table:

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

Try it

Third, get data from the rank_demo table:

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

Try it

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)

Try it

Output:

The first two rows receive 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.

Basic Oracle RANK() function 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 ) rank_value FROM products;Code language: SQL (Structured Query Language) (sql)

Try it

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)

Try it

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)

Try it

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:

Summary #

• Use the RANK() analytic function to calculate the rank of a value in a set of values.
• Use the PARTITION BY clause to divide the result into multiple partitions.
• Use the ORDER BY clause to sort rows in each partition.

Quiz #