What is the RANK function?
The RANK function is an Oracle analytical function which calculates the rank of values which are retrieved, or against a set of rows. The same values will share the same rank for a group of rows or the subsequent ranks as they are tied to the values, so it might not be consecutive in sequence or number, as they may vary in each case.
Analytical functions are really helpful when we try to use them for reporting purpose and helps in taking business decisions. To use the RANK function we need to use the "ORDER BY CLAUSE".
You can learn more about the "Order by Clause" by clicking this link to another one of my articles.
The RANK function will be used after the "ORDER BY" clause is been applied to the rows, as shown in below syntax.
The syntax for RANK Function:
RANK() WITHIN GROUP (ORDER BY <<column name>> DESC/ASC)
In RANK analytical functions we don't need PARTITIONED BY conditions to execute successfully.
Let us take an example on getting the ranks of the employees based on the departments.
SELECT FIRST_NAME
,LAST_NAME , SALARY, DEPARTMENT_ID, RANK() OVER ( ORDER BY salary DESC) "Rank"
from EMPLOYEES WHERE DEPARTMENT_ID =50 ;
Output:
FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID Rank
------------------------------ ------------------------- ---------- --------------- ----------
Adam Fripp 8200 50 1
Matthew Weiss 8000 50 2
Payam Kaufling 7900 50 3
Shanta Vollman 6500 50 4
Kevin Mourgos 5800 50 5
Nandita Sarchand 4200 50 6
Alexis Bull 4100 50 7
Sarah Bell 4000 50 8
Britney Everett 3900 50 9
Kelly Chung 3800 50 10
Renske Ladwig 3600 50 11 Jennifer Dilly 3600 50 11 Trenna Rajs 3500 50 13 Julia Dellinger 3400 50 14
Laura Bissot 3300 50 15
Jason Mallin 3300 50 15
Winston Taylor 3200 50 17
Samuel McCain 3200 50 17
Stephen Stiles 3200 50 17
Julia Nayer 3200 50 17
Curtis Davies 3100 50 21
Jean Fleaur 3100 50 21
From the above output, we can see the that we don't see the RANKS assigned is consecutive in sequence. We can see from the highlighted rows. In this case we see that the RANK 11 is assigned to two employees based on the salary and as they are getting the same salary, it has assigned one rank but the sequence is broken here having 11 in place of 12, which is missing from the assigned ranks and the next number i.e. 13 is assigned to the next row.
Similarly, we see the same scenario for the other rows too. Here we are only checking for a specific department number. In a case where we need to show the similar results for all the departments, then we can use a partition by clause to the RANK function and the SQL query will be as below:
SELECT FIRST_NAME
,LAST_NAME , SALARY, DEPARTMENT_ID, RANK() OVER ( PARTITION by department_id ORDER BY salary DESC) "Rank"
from EMPLOYEES ;
Expected result:
FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID Rank
------------------------------ ------------------------- ---------- --------------- ----------
Jennifer Whalen 4400 10 1
Michael Hartstein 13000 20 1
Pat Fay 6000 20 2
Den Raphaely 11000 30 1
Alexander Khoo 3100 30 2
Shelli Baida 2900 30 3
Sigal Tobias 2800 30 4
Guy Himuro 2600 30 5
Karen Colmenares 2500 30 6
Susan Mavris 6500 40 1
Adam Fripp 8200 50 1
Matthew Weiss 8000 50 2
Payam Kaufling 7900 50 3
Shanta Vollman 6500 50 4
Kevin Mourgos 5800 50 5
Nandita Sarchand 4200 50 6
Alexis Bull 4100 50 7
Sarah Bell 4000 50 8
Britney Everett 3900 50 9
Kelly Chung 3800 50 10
Jennifer Dilly 3600 50 11
Renske Ladwig 3600 50 11
Trenna Rajs 3500 50 13
Julia Dellinger 3400 50 14
Laura Bissot 3300 50 15
Jason Mallin 3300 50 15
Stephen Stiles 3200 50 17
Samuel McCain 3200 50 17
Julia Nayer 3200 50 17
Winston Taylor 3200 50 17
Jean Fleaur 3100 50 21
Curtis Davies 3100 50 21
Alana Walsh 3100 50 21
So now we understand how to use the RANK function in Oracle.
Thank you for reading my article, please feel free to leave me some feedback or to suggest any future topics. I'll be looking forward to hearing from you – Swadhin Ray (Sloba)
For more information about me, please check out my Experts Exchange Profile page.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)