RANK is an Analytical function as well as Aggregate function in Oracle. It returns the rank of the row within the group.
Syntax (Aggregate function):
rank(expression1,expression2,…) WITHIN GROUP (ORDER BY expression1,expression2…)
Example :- Find the rank of the employee whose employee number is 18 and salary is 7700
select rank(7700,18) WITHIN GROUP (ORDER BY salary,empno) as rk from emp;
RK
———-
7
Syntax (Analytical function):
rank() over( [partition by column] order by column )
Examples:-
Consider below EMP table structure
EMPNNO | EMPNAME | SALARY | DEPTNO |
11 | Solomon | 10000 | 5 |
12 | Susan | 10000 | 5 |
13 | Wendy | 9000 | 1 |
14 | Benjamin | 7500 | 1 |
15 | Tom | 7600 | 1 |
16 | Henry | 8500 | 2 |
17 | Robert | 9500 | 2 |
18 | Paul | 7700 | 2 |
19 | Dora | 8500 | 3 |
20 | Samuel | 6900 | 3 |
21 | Mary | 7500 | 3 |
22 | Daniel | 6500 | 4 |
23 | Ricardo | 7800 | 4 |
24 | Mark | 7200 | 4 |
1. Find the salary ranks for all employees.
select salary,rank() over(order by salary asc) as sal_rank from emp;
SALARY SAL_RANK
———- ———-
6500 1
6900 2
7200 3
7500 4
7500 4
7600 6
7700 7
7800 8
8500 9
8500 9
9000 11
9500 12
10000 13
10000 13
12000 15
Note that rank function will assign same number if there is a tie and it will skip those many numbers.
From the above example there are two employees have salary of 7500 and rank assigned in 4 and next rank is 6. So it skipped rank number 5. You can use DENSE_RANK() function for without skipping numbers.
Read DENSE_RANK() function also
2. Find the highest salary holder in each department.
select * from
(
select empno,salary,
rank() over(partition by deptno order by salary desc) as sal_rank from emp
)
where sal_rank = 1;
EMPNO SALARY SAL_RANK
———- ———- ———-
13 9000 1
17 9500 1
19 8500 1
23 7800 1
10 12000 1
Greetings from California! I’m bored at work so I decided to
browse your blog on my iphone during lunch break.
I enjoy the knowledge you provide here and can’t wait to take
a look when I get home. I’m amazed at how quick your blog
loaded on my cell phone .. I’m not even using WIFI, just 3G
.. Anyways, amazing blog!
Thanks for comment
Can you please explain the 2nd Sql Query why did we use partition by deptno order by salary desc…?
to find highest salary in each dept