How to use Rank( ), Dense_Rank( ) and Row_Number( ) function

Understanding Row Number Function

Here we will see the Row_Number(), Rank() and Dense_Rank()
function with details and example

Look at the Figure 1  which has some data and in next
figure 2 we are going to explain about the use of above mentioned function




Above figure has query and result we can see that there are 14
rows with column jobid, job_disc, minimum value and maximum
value.


Now see figure 2




Using ROW_NUMBER() Function

In figure 2 we can see the result of ROW_NUMBER() function, it just a sequential order starts from 1 to the number of rows returns within a result set.

Using RANK() Function

In figure 2 we can see the result of RANK() function, If result of two or more rows are same then it will give same rank and put a gap of repeated no of rows in next sequential number for next row. As we can see in figure 2 for row number 2,3 and 4 has same rank as 2, and for row number 5 it direct assign rank 5 and eliminate rank 3 and 4 because it's repeated in previous row.

Using DENSE_RANK() Function

In figure 2 we can see the result of DENSE_RANK() function, if result of two or more rows are same then it will assign same rank but for the next rank of next row it will not put any gap and assign continuous number to the next rank of next row. Like above example it will not eliminate rank 3 and 4. It will assign rank 3 for row number 5th.