RANK & DENSE_RANK

Thanks to my new friend, Susanta, I got an opportunity to learn something new in SQL. Fortunately for me, Purna, as always, was at hand, to help and we took help also from Purna’s thatha i.e; Google.

Jokes apart, here’s the problem:
There is a student table with columns name and marks. We need to find the names of all students who have got third rank.
Solution:-- Suppose we want names of all students
-- along with their ranks in classes -- using the "MARKS" column
-- Ordered by the ranks
-- Using RANK() function, we need to explicitly sort the ranks
SELECT NAME
, marks
, RANK () OVER (ORDER BY marks) student_rank
FROM student_table
ORDER BY student_rank;-- Using DENSE_RANK() function,
-- the sorting of the ranks is done automatically
SELECT NAME
, marks
, DENSE_RANK () OVER (ORDER BY marks) student_rank
FROM student_table ;
-- Suppose we want the names of all students
-- who have got third rank,
-- we can write the query like this
-- by using RANK() functionselect * from
(SELECT NAME
, marks
, RANK () OVER (ORDER BY marks) student_rank
FROM student_table
ORDER BY student_rank) temporary_table
where student_rank=3--Using DENSE_RANK function,
--it can be written like this
select * from
(SELECT NAME
, marks
, DENSE_RANK () OVER (ORDER BY marks) student_rank
FROM student_table) temporary_table
where student_rank=3
So, here’s more about RANK and DENSE_RANK functions, courtesy, http://www.techonthenet.com/


Oracle/PLSQL: Rank Function

In Oracle/PLSQL, the rank function returns the rank of a value in a group of values. It is very similar to the DENSE_RANK function. However, the rank function can cause non-consecutive rankings if the tested values are the same. Whereas, the DENSE_RANK function will always result in consecutive rankings.
The rank function can be used two ways - as an Aggregate function or as an Analytic function.

Syntax #1 - Used as an Aggregate FunctionAs an Aggregate function, the rank returns the rank of a row within a group of rows.

The syntax for the rank function when used as an Aggregate function is:
rank( expression1, ... expression_n )
WITHIN GROUP ( ORDER BY expression1, ... expression_n )
expression1 .. expression_n can be one or more expressions which identify a unique row in the group.

Note:
There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

For Example:select rank(1000, 500) WITHIN GROUP (ORDER BY salary, bonus)
from employees;The SQL statement above would return the rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

Syntax #2 - Used as an Analytic FunctionAs an Analytic function, the rank returns the rank of each row of a query with respective to the other rows.

The syntax for the rank function when used as an Analytic function is:
rank() OVER ( [ query_partition_clause] ORDER BY clause )

For Example:select employee_name, salary,
rank() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = 'Marketing';
The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the rank function would return the same rank for both employees.

However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is quite different from the DENSE_RANK function which generates consecutive rankings.

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


Oracle/PLSQL: Dense_Rank Function
In Oracle/PLSQL, the dense_rank function returns the rank of a row in a group of rows. It is very similar to the RANK function. However, the RANK function can cause non-consecutive rankings if the tested values are the same. Whereas, the dense_rank function will always result in consecutive rankings.
The dense_rank function can be used two ways - as an Aggregate function or as an Analytic function.

Syntax #1 - Used as an Aggregate Function
As an Aggregate function, the dense_rank returns the dense rank of a row within a group of rows.
The syntax for the dense_rank function when used as an Aggregate function is:
dense_rank( expression1, ... expression_n )
WITHIN GROUP ( ORDER BY expression1, ... expression_n )
expression1 .. expression_n can be one or more expressions which identify a unique row in the group.
Note:There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

For Example:
select dense_rank(1000, 500)
WITHIN GROUP (ORDER BY salary, bonus)
from employees;
The SQL statement above would return the dense rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.
Syntax #2 - Used as an Analytic FunctionAs an Analytic function, the dense_rank returns the rank of each row of a query with respective to the other rows.

The syntax for the dense_rank function when used as an Analytic function is:
dense_rank() OVER ( [ query_partition_clause] ORDER BY clause )

For Example:select employee_name, salary,
dense_rank() OVER (PARTITION BY department ORDER BY salary)from employees
where department = 'Marketing';The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the dense_rank function would return the same rank for both employees.

1. Reference: http://www.techonthenet.com/oracle/functions/dense_rank.php