Ranking Function - Oracle Analytical Functions

Analytical Function has following basic syntax:
function(argument) OVER(Analytic Clause)

The arguments of the function can be empty as in the below example;

Example1
Select RANK() OVER (ORDER BY SALARY), EMPLOYEEID
FROM EMPLOYEE

This query will generate the rank for each rows based on the salary. The Order by query in the analytical clause is similar to the ordinary Order by. So it will default to Ascending.

The in the above example will contain an Ordering, Partitioning or Windowing.

The advantages of Rank function over the traditional ranking queries.

We can generate the ROWNUM and then compute the RANK based on that. It is somehow tricky. Following are the problems with ROWNUM.

1) ROWNUM is computed as rows are retrieved from the database.
2) The Problem with ROWNUM is that It operates before the Order By sorting is executed.

We can achieve using ordinary query like this.

SELECT NAME, SALARY, ROWNUM(RANK) FROM
(SELECT NAME, SALARY FROM SOMA ORDER BY SALARY);

However the above query won’t solve the Ranking problem. Since we are showing the ROWNUM as the Rank. It won’t handle 2 rows if the 2 rows are having same salary.

There are some analytical functions to handle this Ranking issue : RANK(), ROW_NUMBER(), DENSE_RANK. We will see about these functions one by one.

1) ROW_NUMBER function:

SELECT EMPNAME. EMPNUM, SALARY, ROW_NUMBER() OVER (ORDER BY SALARY DESC) TOP_RANK FROM EMPLOYEE
ORDER BY EMPNAME;

Analytical Functions will generate a Ordering by themselves. Although it is useful, we have to be careful about the final result set ordering. So I am adding another ORDER BY to the external query to sort based on Employee name. The above query can be replicated using the ordinary query as follows.

SELECT EMPNAME, EMPNUM, SALARY, ROWNUM as RANK
FROM
(SELECT EMPNAME, EMPNUM, SALARY FROM
EMPLOYEE ORDER BY SALARY DESC)
ORDER BY EMPNAME
We can use RANK and DENSE_RANK function as the way we use ROW_NUMBER() function.

2) RANK() function:

Rank function will produce the row numbering and skip the rank if there is a tie.

SELECT
EMPNO, EMPNAME , SALARY,
RANK() OVER (ORDER BY SALARY DESC) RANK
FROM
EMPLOYEE

The result set of this query will be like this.
EMPNO EMPNAME SALARY RANK
1304 SOMA 10000 1
1309 SATHISH 9500 2
1410 SATHYA 9500 2
1516 SUNDAR 8000 4

3) DENSE_RANK() Function

DENSE_RANK Function acts similar to Rank function but instead of ranking the tied rows and move up to the next rank beyond the tie. It will not skip the next rank level.

SELECT
EMPNO, EMPNAME , SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) RANK
FROM
EMPLOYEE

The result set of this query will be like this.

EMPNO EMPNAME SALARY RANK
1304 SOMA 10000 1
1309 SATHISH 9500 2
1410 SATHYA 9500 2
1516 SUNDAR 8000 3