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