Db2 RANK

Summary: in this tutorial, you will learn how to use the Db2 RANK() function to assign a rank to each row in a result set.

Introduction to the Db2 RANK() function

The RANK() is a window function that calculates the rank of a row in a set of rows. The RANK() returns the same rank for the rows with the same value.

Because RANK() adds the number of tied rows to the tied rank to calculate the next rank, the ranks may not be sequential. In other words, there may have gaps in the sequential rank numbering.

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

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

In this syntax:

partition_clause

The partition_clause divides rows into partitions (or groups) to which the RANK() is applied:

PARTITION BY expression1 
            [, expression2, ...]
Code language: SQL (Structured Query Language) (sql)

The partition_clause is optional. If you skip it, the RANK() will treat the whole result set as a single partition.

order_by_clause

THe order_by_clause specifies the order of rows in partitions by one or more sort keys:

ORDER BY 
    sort_expression1 [ASC | DESC ] [NULLS FIRST | NULLS LAST]
    [, sort_expression2 [ASC | DESC ] [NULLS FIRST | NULLS LAST]]
Code language: SQL (Structured Query Language) (sql)

By default, the ORDER BY clause sorts rows in each partition in ascending order, or ASC.

The NULLS FIRST or NULLS LAST determines whether the NULL values appear before or after non-NULL values in the sort order.

Db2 RANK() illustration

First, create a new table called rank_samples:

CREATE TABLE rank_samples(
    val VARCHAR(1) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the rank_samples table:

INSERT INTO rank_samples(val)
VALUES('A'),('A'),('B'),('C'),('D'),('D');
Code language: SQL (Structured Query Language) (sql)

Third, show the contents of the rank_samples table:

SELECT val FROM rank_samples;
Code language: SQL (Structured Query Language) (sql)

Finally, use the RANK() function to calculate the rank for each row in the rank_samples table:

SELECT 
    val, 
    RANK() OVER( 
        ORDER BY val
    ) val_rank 
FROM 
    rank_samples;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

The first and second rows have the same value, therefore, they receive the same rank 1.

The third row receives rank number 3, not 2 because the first two rows are already assigned a rank.

The fourth row receives rank 4 which is the rank of the third row plus one.

The fifth and sixth rows receive the same rank number 5 because they have the same value.

Practical Db2 RANK() examples

Let’s take some examples of using the RANK() function. We’ll use the books table from the sample database for the demonstration:

1) Using Db2 RANK() function to rank books by ratings

The following example uses the RANK() function to rank books of the publisher id 5 by ratings:

SELECT 
    title, 
    rating, 
    RANK() OVER( 
        ORDER BY rating DESC
    ) rating_rank
FROM 
    books
WHERE 
    publisher_id = 5;
Code language: SQL (Structured Query Language) (sql)

Output:

As can be seen clearly from the output, the books with the same rating received the same rank numbers.

2) Using Db2 RANK() function for the top-N query

The following example uses the RANK() function to get the top 10 best books by the publisher id 5:

WITH cte_books AS (    
    SELECT 
        title, 
        rating, 
        RANK() OVER( 
            ORDER BY rating DESC
        ) rating_rank
    FROM 
        books
    WHERE 
        publisher_id = 5
)
SELECT 
    * 
FROM 
    cte_books 
WHERE
    rating_rank <=10;
Code language: SQL (Structured Query Language) (sql)

Output:

3) Using Db2 RANK() function with PARTITION BY clause example

The following example uses the RANK() function to get the top 5 best books by each publisher:

WITH cte_books AS (    
    SELECT 
        title, 
        rating, 
        publisher_id,
        RANK() OVER( 
            PARTITION BY publisher_id
            ORDER BY rating DESC
        ) rating_rank
    FROM 
        books
)
SELECT * 
FROM cte_books 
WHERE rating_rank <=5;
Code language: SQL (Structured Query Language) (sql)

Output:

In this tutorial, you have learned how to use the Db2 RANK() function to assign a rank to each row in a result set.