NULLS LAST/FIRST

When we use RANK function and if we have the ranking criteria column with NULL values, they do have chances to be listed in first position. By using Nulls last we can avoid such occurrences.

Ex:

Student ranking first based on their achieved grades on all test they appeared.

select p.name Student, m.name, m.tests, rank() over ( order by tests desc ) as S_rank from ptudents p, test m where p.name = m.student_name(+) and p.name in ('M','V','P','M','N') order by s_rank

Student	NAME	tests	S_RANK
V	-	-	1
M	-	-	1
N	T1	1353.4	3
P	C1	603.6	4
N	P1	210	5
N	N1	170	6

select p.name Student, m.name, m.tests, rank() over ( order by tests desc NULLS LAST ) as S_rank
from ptudents p, test m where p.name = m.student_name(+) and p.name in ('M','V','P','M','N') order by s_rank

Student	NAME	tests	S_RANK
N	T1	1353.4	1
P	C1	603.6	2
N	P1	210	3
N	N1	170	4

Ans null will be listed in last

More on Ranking Functions

More on Ranking:

We have seen RANK(),DENSE_RANK(),ROW_NUMBER in our previous post, lets see about CUME_DIST,PERCENT_RANK() and NTILE

PERCENT_RANK:- Given with the details of sales made in a year for n number of items and list as per sales percentage.

(rank of row-1)/(total number of rows -1)=Percent_rank

select name, gross_sales,

       100*percent_rank() over ( order by gross_sales ) as pctrank

from movies

order by 2,name

Name   Gross Sales         PCTRank

Abc        35                           1

Efg          33                           2.344

Igh          30                           3.344

CUME_DIST: For cumulative ranking often used for graphical purpose.

select name, gross_sales,

100*percent_rank() over ( order by gross_sales ) as pctrank

       100*cume_dist() over ( order by gross_sales ) as cumedist

from movies

order by 2,name

Name   Gross Sales         PCTRank        Cumedist

Abc        35                           1                              1

Efg          33                           2.344                  2

Igh          30                           2.346                  2

Yhi          29                           2.12                        3

NTILE(n):-Segregate the data as per the set of records given to n-tile value

select name, gross_sales,

       ntile(3) over ( order by gross_sales desc ) as quartile

from movies

order by 2,nam Name   Gross Sales         PCTRank        Cumedist

Abc        35                           1                              1                              1

Efg          33                           2.344                  2                              1

Igh          30                           2.346                  2                              2

Yhi          29                           2.12                        3                              2

Abc1      29                           1                              1                              3

Efg2       28                           2.344                  2                                3