Oracle Analytical Ranking functions

Oracle Analytical Ranking functions-Learn about few of analytical ranking functions like RANK and DENSE_RANK, ROW_NUMBER , NTILE FUNCTION, FIRST_VALUE, LAST_VALUE, NTH_VALUE with fully explained examples.

Oracle Analytical Ranking functions

ROW_NUMBER

ROW_NUMBER function returns the numbers of a row with in a result set starting with 1 for the first row and  returns the sequential rank for the values with out any gaps if the duplicate values exist.

For Example , find  records with same name in below table.

NAME
SMITH
ALLEN
SMITH
ALLEN
WARD

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

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

ROW NUMBER FUNCTION SYNTAX

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

PARTITION BY divides the result set into groups to which the ROW_NUMBER() function is applied.
ORDER BY specifies the order in which the sequential ROW_NUMBER() is assigned.

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

     SELECT STDNO,SNAME,FEE,
            ROW_NUMBER() 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	4
7369	SMITH	8000	5
7566	JONES	2975	6
7844	TURNER	1500	7

ROW_NUMBER function with PARTITION BY

SELECT STDNO,
  SNAME,
  FEE,
  DEPTNO,
  ROW_NUMBER() over( partition BY DEPTNO order by FEE DESC)AS RANK
FROM STD;

Output

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	4
7844	TURNER	1500	3	5

NOTE:

Row_number assigns unique sequential number to each row.

NTILE FUNCTION

The  NTILE  analytic function allows you to break a result set into a specified number of approximately equal groups, or buckets, rows permitting. If the number of rows in the set is smaller than the number of buckets specified, the number of buckets will be reduced so there is one row per bucket.

Syntax

SELECT  column_name,NTILE  (Number_Expression)OVER  ([partition_Clause]ORDER  BY  )
FROM  table_name;

Number_Expression:  a numeric value that decides the number of groups to be created.
Partition_Clause:  divides the result given by the FROM Clause with the help of the OVER method.
Partition_Clause:  divides the result given by the FROM Clause with the help of the OVER method.

Query:

SELECT 
SNAME,
FEE,
NTILE(2) over (order by FEE DESC) AS [NTILE] 
FROM STD;

NTILE function with PARTITION BY

SELECT SNAME,
  FEE,
  deptno,
  NTILE(2) over (partition BY deptno order by FEE DESC)
FROM STD;

Examples to find the Nth Highest Fee Payer.

Example 1: using Normal query
Syntax :

SELECT DISTINCT A.SNAME ,
  A.FEE
FROM STD A
WHERE N-1=
  (SELECT COUNT(DISTINCT B.FEE) FROM STD B WHERE B.FEE> A.FEE
  )
ORDER BY 1 DESC;

or 
SELECT DISTINCT sname,
  A.FEE
FROM STD A
WHERE N=
  (SELECT COUNT(DISTINCT B.FEE) FROM STD B WHERE A.FEE<= B.FEE
  )
ORDER BY 1 DESC;

Query to find the 2nd highest fee Payer

SELECT DISTINCT A.FEE
FROM STD A
WHERE 2=
  (SELECT COUNT(DISTINCT B.FEE) FROM STD B WHERE A.FEE<=B.FEE
  )
ORDER BY 1 DESC;

or

SELECT DISTINCT sname,
  A.FEE
FROM STD A
WHERE 2=
  (SELECT COUNT(DISTINCT B.FEE) FROM STD B WHERE A.FEE<= B.FEE
  )
ORDER BY 1 DESC;

Note
to find the Nth highest value Replace N with Corresponding Number

Example 2: using rank method

syntax:

SELECT *
FROM
  (SELECT s.* ,RANK() OVER (ORDER BY FEE DESC) AS NTHHIGESTFEEPAY FROM STD S
  )
WHERE NTHHIGESTFEEPAY=N;

Query to find the 2nd highest fee Payer

SELECT *
FROM
  (SELECT s.* ,RANK() OVER (ORDER BY FEE DESC) AS NTHHIGESTFEEPAY FROM STD S
  )
WHERE NTHHIGESTFEEPAY=2;

Example 3: using dense rank method
syntax

SELECT *
FROM
  (SELECT s.* ,
    DENSE_RANK() OVER (ORDER BY FEE DESC) AS NTHHIGESTFEEPAY
  FROM STD S
  )
WHERE NTHHIGESTFEEPAY=N;

Query to find the 2nd highest fee Payer

SELECT *
FROM
  (SELECT s.* ,
    DENSE_RANK() OVER (ORDER BY FEE DESC) AS NTHHIGESTFEEPAY
  FROM STD S
  )
WHERE NTHHIGESTFEEPAY=2;

Example 4: using ROW_NUMBER method
Syntax

SELECT *
FROM
  (SELECT S.*,
    ROW_NUMBER() over ( order by fee DESC) AS NTHHIGESTFEEPAY
  FROM STD S
  )
WHERE NTHHIGESTFEEPAY=N;

Query to find the 2nd highest fee Payer

SELECT *
FROM
  (SELECT S.*,
    ROW_NUMBER() over ( order by fee DESC) AS NTHHIGESTFEEPAY
  FROM STD S
  )
WHERE NTHHIGESTFEEPAY=2;

Draw back
ROW_NUMBER cannot act as same like dense rank method.

Example 5: using rownum key word
Syntax

SELECT *
FROM
  (SELECT STDNO, FEE,rownum NTHHIGESTFEEPAY FROM STD ORDER BY FEE DESC
  )
WHERE NTHHIGESTFEEPAY = N;

Query to find the 2nd highest fee Payer

SELECT *
FROM
(SELECT STDNO, FEE,rownum NTHHIGESTFEEPAY FROM STD ORDER BY FEE DESC
)
WHERE NTHHIGESTFEEPAY = 2;

Example 6: using Level Keyword

Syntax:

SELECT LEVEL,  MAX(FEE) FEE from STD
WHERE LEVEL           =N
 connect by prior FEE>FEE
GROUP BY LEVEL;

Query to find the 2nd highest fee Payer

SELECT LEVEL,  MAX(FEE) FEE from STD
WHERE LEVEL           =2
 connect by prior FEE>FEE
GROUP BY LEVEL;

Drawback
Complete details like student name, department etc. cannot find using Level keyword.