Oracle RANK function

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

 

5 responses to “Oracle RANK function”

  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!

  2. Can you please explain the 2nd Sql Query why did we use partition by deptno order by salary desc…?

    1. to find highest salary in each dept

  3. to find highest salary in each dept