diving_2

Diving into Oracle Analytical Functions part 2/2

Hello all and welcome back for the second part of this post !
As promised, on the previous post , I will cover RANK function, DENSE RANK and ROW_NUMBER analytical functions.

RANK

The rank function allows us to rank a value in a group of values.The Oracle rank function comes in “two flavors” , the aggregate flavor and the analytical flavor.The only problem when using this function is that it can return non-consecutive rankings if the tested values are the same.But before jumping to the examples, lets check the syntax for the aggregate flavor.

Aggregate flavor Syntax

  • rank( expression1, … expression_n ) WITHIN GROUP ( ORDER BY expression1, … expression_n )

Now , lets imagine that we want to rank an employee with salary 3100  in the  employees table from HR schema(Oracle 11g).  We could issue :

select rank(3100) WITHIN GROUP (ORDER BY salary) rank_result from employees;

This would return :

However if we issue :

select distinct e.salary from employees e order by e.salary asc;

we would get the following result :

As this example shows, the rank “27”  isn’t really correct.  The actual rank for salary 3100 is , as we can see in the picture, 10. This happens because there are repeated salary values for different employees causing non-consecutive rankings to be returned. When using the dense_rank function further down the page, we will clearly see that a similar  query will correctly return rank 10.

Analytical flavor syntax

  • rank() OVER ( [ query_partition_clause] ORDER BY clause )

With the analytical flavor we can take the previous example a step ahead. Oracle analytical functions allow us to group results, so lets try to retrieve the rank not for one employee but for all employees who work in department 50. We could issue

select  e.first_name,
e.salary,
e.department_id,
rank() over(partition by e.department_id order by salary) rank_function
from employees e
where e.department_id = 50
order by 3;

Which would return

As you can clearly see , non-consecutive values are being returned. This will be different in the example below with dense rank!

For more info

http://techonthenet.com/oracle/functions/rank.php

DENSE_RANK

Unlike the rank function that can cause(as we have seen) non-consecutive rankings if the tested values are the same, the Oracle dense_rank function will always return consecutive rankings. Similarly to rank,dense_rank comes in two flavors.  We will start with the aggregate flavor.

Aggregate flavor Syntax

  • dense_rank( expression1, … expression_n ) WITHIN GROUP ( ORDER BY expression1, … expression_n )

Continuing the previous example of the rank function lets issue :

select dense_rank(3100) WITHIN GROUP (ORDER BY salary) dense_rank_result
from employees;

The result would be

This was the expected result, because dense_rank always returns consecutive rankings.  Lets also try another approach.   Imagine you want to rank employees whose salary is 11000 . You can achieve this in two ways.

1st way

select  e.first_name, e.last_name,e.salary salary,
dense_rank(11000) WITHIN GROUP (ORDER BY e.salary) sal_rank
from employees e
group by e.first_name, e.last_name, e.salary
order by sal_rank , e.salary; 

Would produce the following result :

The order by clause defines the rank classification.Default ascending order after the within group clause means salaries >=11000 will be ranked first and salaries<11000 will be ranked afterwards

2nd way

select  e.first_name, e.last_name,e.salary
dense_rank(11000) WITHIN GROUP (ORDER BY e.salary ASC) sal_rank
from employees e
group by e.first_name, e.last_name,e.salary
order by sal_rank , e.salary; 

Would produce the following result:

Changing the order by clause after the within group clause, changes the rank classification order. This means that salaries<=11000 will be ranked first and salaries>11000 will be ranked afterwards.

Now that we have understood the concept behind the aggregate flavor lets check the analytical flavor.

Analytical flavor syntax

  • dense_rank() OVER ( [ query_partition_clause] ORDER BY clause )

Now lets take the example from the oracle rank function and combine it with the dense_rank function. We would issue :

select  e.first_name name,
e.salary sal,
e.department_id dept_id,
rank() over(partition by e.department_id order by salary) rank_funct,
dense_rank() over(partition by e.department_id order by salary) dense_rank_funct
from employees e
where e.department_id = 50
order by 3;

And the result would be

As you can see, whenever the department_id changes, our rank is also reset . Also as expected, the dense_rank function is also returning consecutive values.This print shows clearly the difference between rank and dense rank analytical functions.

For more info

http://techonthenet.com/oracle/functions/dense_rank.php

ROW_NUMBER

The oracle function row_number assigns a unique number to each row it is assigned.Unlike the other functions, this one comes only with an analytical flavor.

Analytical flavor Syntax

  • row_number  OVER ( [ query_partition_clause] ORDER BY clause )

We could use this oracle analytical function to assign an unique number to each result returned from the dense_rank example. First we would modify the dense rank query to include the row_number function. It would look like this :

select  e.first_name name,
e.salary sal,
e.department_id dept_id,
rank() over(partition by e.department_id order by salary) rank_funct,
dense_rank() over(partition by e.department_id order by salary) dense_rank_funct,
row_number() over(partition by e.department_id order by salary) row_nbr_funct
from employees e
where e.department_id = 50
order by 3;

The result would be

As we can see, all rows appear now properly numbered .

For more info

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm

Conclusion

As you could see , knowing these functions represents a great benefit for the Oracle Developer, so make some time to study them in depth!

Cheers,
Carlos

For more info on all topics :

http://psoug.org/reference/analytic_functions.html
http://www.orafaq.com/node/55