{"id":984,"date":"2020-02-10T20:13:17","date_gmt":"2020-02-11T04:13:17","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=984"},"modified":"2020-04-11T06:57:39","modified_gmt":"2020-04-11T14:57:39","slug":"db2-rank","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-window-functions\/db2-rank\/","title":{"rendered":"Db2 RANK"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 RANK()<\/code> function to assign a rank to each row in a result set.<\/p>\n\n\n\n

Introduction to the Db2 RANK() function<\/h2>\n\n\n\n

The RANK()<\/code> is a window function<\/a> that calculates the rank of a row in a set of rows. The RANK()<\/code> returns the same rank for the rows with the same value.<\/p>\n\n\n\n

Because RANK()<\/code> 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.<\/p>\n\n\n\n

The following shows the syntax of the RANK()<\/code> function:<\/p>\n\n\n

RANK() OVER ([partition_clause] order_by_clause)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
partition_clause<\/h3>\n\n\n\n
The partition_clause<\/code> divides rows into partitions (or groups) to which the RANK()<\/code> is applied:<\/p>\n\n\n
PARTITION BY expression1 \n            [, expression2, ...]\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The partition_clause<\/code> is optional. If you skip it, the RANK()<\/code> will treat the whole result set as a single partition.<\/p>\n\n\n\n
order_by_clause<\/h3>\n\n\n\n
THe order_by_clause<\/code> specifies the order of rows in partitions by one or more sort keys:<\/p>\n\n\n
ORDER BY \n    sort_expression1 [ASC | DESC ] [NULLS FIRST | NULLS LAST]\n    [, sort_expression2 [ASC | DESC ] [NULLS FIRST | NULLS LAST]]\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
By default, the ORDER BY<\/code> clause sorts rows in each partition in ascending order, or ASC<\/code>.<\/p>\n\n\n\n
The NULLS FIRST<\/code> or NULLS LAST<\/code> determines whether the NULL values appear before or after non-NULL values in the sort order.<\/p>\n\n\n\n
Db2 RANK() illustration<\/h2>\n\n\n\n
First, create a new table<\/a> called rank_samples<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> rank_samples(\n    val VARCHAR<\/span>(1<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> rank_samples(val)\nVALUES<\/span>('A'<\/span>),('A'<\/span>),('B'<\/span>),('C'<\/span>),('D'<\/span>),('D'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, show the contents of the rank_samples<\/code> table:<\/p>\n\n\n
SELECT<\/span> val FROM<\/span> rank_samples;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, use the RANK()<\/code> function to calculate the rank for each row in the rank_samples<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    val, \n    RANK<\/span>() OVER<\/span>( \n        ORDER<\/span> BY<\/span> val\n    ) val_rank \nFROM<\/span> \n    rank_samples;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
The first and second rows have the same value, therefore, they receive the same rank 1.<\/p>\n\n\n\n
The third row receives rank number 3, not 2 because the first two rows are already assigned a rank.<\/p>\n\n\n\n
The fourth row receives rank 4 which is the rank of the third row plus one.<\/p>\n\n\n\n
The fifth and sixth rows receive the same rank number 5 because they have the same value.<\/p>\n\n\n\n
Practical Db2 RANK() examples<\/h2>\n\n\n\n
Let’s take some examples of using the RANK()<\/code> function. We’ll use the books<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using Db2 RANK() function to rank books by ratings<\/h3>\n\n\n\n
The following example uses the RANK()<\/code> function to rank books of the publisher id 5 by ratings:<\/p>\n\n\n
SELECT<\/span> \n    title, \n    rating, \n    RANK<\/span>() OVER<\/span>( \n        ORDER<\/span> BY<\/span> rating DESC<\/span>\n    ) rating_rank\nFROM<\/span> \n    books\nWHERE<\/span> \n    publisher_id = 5<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
As can be seen clearly from the output, the books with the same rating received the same rank numbers.<\/p>\n\n\n\n
2) Using Db2 RANK() function for the top-N query<\/h3>\n\n\n\n
The following example uses the RANK()<\/code> function to get the top 10 best books by the publisher id 5:<\/p>\n\n\n
WITH<\/span> cte_books AS<\/span> (    \n    SELECT<\/span> \n        title, \n        rating, \n        RANK<\/span>() OVER<\/span>( \n            ORDER<\/span> BY<\/span> rating DESC<\/span>\n        ) rating_rank\n    FROM<\/span> \n        books\n    WHERE<\/span> \n        publisher_id = 5<\/span>\n)\nSELECT<\/span> \n    * \nFROM<\/span> \n    cte_books \nWHERE<\/span>\n    rating_rank <=10<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
3) Using Db2 RANK() function with PARTITION BY clause example<\/h3>\n\n\n\n
The following example uses the RANK()<\/code> function to get the top 5 best books by each publisher:<\/p>\n\n\n
WITH<\/span> cte_books AS<\/span> (    \n    SELECT<\/span> \n        title, \n        rating, \n        publisher_id,\n        RANK<\/span>() OVER<\/span>( \n            PARTITION<\/span> BY<\/span> publisher_id\n            ORDER<\/span> BY<\/span> rating DESC<\/span>\n        ) rating_rank\n    FROM<\/span> \n        books\n)\nSELECT<\/span> * \nFROM<\/span> cte_books \nWHERE<\/span> rating_rank <=5<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the Db2 RANK()<\/code> function to assign a rank to each row in a result set.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Second, insert some rows<\/a> into the rank_samples<\/code> table:<\/p>\n\n\n