ORACLE Rank and Dense_rank functions

ORACLE Rank and Dense_rank functions – Learn how to calculate nth maximum and minimum value in the result set  based on the ranking mechanism either in ascending order or descending order using Rank and Dens_Rank functions.

Below is the table named STD to perform Ranking operations.

STDNO	NAME	JOINDATE	FEE	DEPTNO
7369	SMITH	17-DEC-80 12.00.00 AM	8000	1
7499	ALLEN	20-FEB-81 12.00.00 AM	16000	3
7521	WARD	22-FEB-81 12.00.00 AM	12500	3
7566	JONES	02-APR-81 12.00.00 AM	2975	2
7654	MARTIN	28-SEP-81 12.00.00 AM	12500	3
7698	BLAKE	01-MAY-81 12.00.00 AM	28500	3
7844	TURNER	08-SEP-81 12.00.00 AM	1500	3

ORACLE Rank and Dense_rank functions

RANK

Rank function returns the rank of each row or rank of values in a group of values in result set.
Rank function gives ranks in non consecutive order if row contains same values.
Rank assigns the same rank to identical values.

For Example consider that there are same names in the table as shown below.

NAME
SMITH
ALLEN
SMITH
ALLEN
WARD

Rank returns the 1,1,3,3,5 as result for above example based on the name column.

NAME	RANK
ALLEN	1
ALLEN	1
SMITH	3
SMITH	3
WARD	5

RANK() FUNCTION SYNTAX

Rank() OVER ([PARTITION BY [COLUMN_NMAE]] ORDER BY [COLUMN_NMAE] [ASC] OR [DESC])

Here is the example to give the rank for highest fee payers in the STD table.

SELECT STDNO,SNAME,FEE,RANK() OVER(ORDER BY FEE DESC)AS RANK FROM STD;

NOTE

RANK Function will assign same number as rank if there exists a same value and will skip those many ranks when giving rank to another value.

Output

STDNO	NAME	FEE	RANK
7698	BLAKE	28500	1
7499	ALLEN	16000	2
7521	WARD	12500	3
7654	MARTIN	12500	3
7369	SMITH	8000	5
7566	JONES	2975	6
7844	TURNER	1500	7

Rank function with PARTITION BY

here is the example to give the rank for highest fee payer based on department wise.

SELECT STDNO,SNAME,FEE,deptno,RANK() OVER( partition by deptno ORDER BY FEE DESC)AS RANK FROM STD;

STDNO	NAME	FEE	DEPTNO	RANK
7369	SMITH	8000	1	1
7566	JONES	2975	2	1
7698	BLAKE	28500	3	1
7499	ALLEN	16000	3	2
7521	WARD	12500	3	3
7654	MARTIN	12500	3	3
7844	TURNER	1500	3	5

DENSE RANK()

Dense Rank function returns the rank of each row or rank of values in a group of values in result set.
Dense Rank function gives ranks in consecutive order without gaps in the ranking.

Rank assigns the same rank to identical values.

For Example consider that there are same names in the table as shown below.

NAME
SMITH
ALLEN
SMITH
ALLEN
WARD

Rank returns the 1,1,2,2,3 as result for above example based on the name column.

NAME	RANK
ALLEN	1
ALLEN	1
SMITH	2
SMITH	2
WARD	3

DENSE RANK() FUNCTION SYNTAX

DENSE_RANK() OVER ([PARTITION BY [COLUMN_NMAE]] ORDER BY [COLUMN_NMAE] [ASC] OR [DESC])

Here is the example to give the rank for highest fee payers in the STD table.

SELECT STDNO,SNAME,FEE,dense_RANK() OVER(ORDER BY FEE DESC)AS RANK FROM STD;

Output

STDNO	NAME	FEE	RANK
7698	BLAKE	28500	1
7499	ALLEN	16000	2
7521	WARD	12500	3
7654	MARTIN	12500	3
7369	SMITH	8000	4
7566	JONES	2975	5
7844	TURNER	1500	6

NOTE

Dense_RANK Function will assign same number as rank if there exists a same value and will give rank in consecutive order without gaps in the ranking.

DENSE_Rank function with PARTITION BY

here is the example to give the rank for highest fee payer based on department wise.

SELECT STDNO,SNAME,FEE,DEPTNO,dense_RANK() OVER( partition by DEPTNO ORDER BY FEE DESC)AS RANK FROM STD;

STDNO	NAME	FEE	DEPTNO	RANK
7369	SMITH	8000	1	1
7566	JONES	2975	2	1
7698	BLAKE	28500	3	1
7499	ALLEN	16000	3	2
7521	WARD	12500	3	3
7654	MARTIN	12500	3	3
7844	TURNER	1500	3	4