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

 

Advertisements

4 thoughts on “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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s