Difference between RANK() and DENSE_RANK() functions?

Question: What's the difference between RANK() and DENSE_RANK() functions?

Answer: Oracle 9i introduced these two functions and they are used to rank the records of a table based on column(s). The syntax of using these functions in SQL queries is 'RANK()/DENSE_RANK() OVER (ORDER BY )'. Here the ORDER BY clause decides which all columns (and in what order) will be used to group and rank the records.

The default sorting order is 'Ascending Order' and if we want we may specify 'DESC' to have the Descending Sort Order. The ranks start from 1 and not from 0.

Handling of NULL values

We may use either NULLS FIRST or NULLS LAST clause to specify the position of the NULL values in the ordered sequence. With Ascending Sort Order, if we specify NULLS FIRST clause then that means we want all the NULLs to be treated as smaller than the Non-NULL values and hence they are positioned right at the top above all the Non-NULL values whereas NULLS LAST clause would mean in this case that the NULLs are larger than all the Non-NULL values and thereby they would be positioned at the bottom of the sequence after the largest Non-NULL value. Exactly the reverse happens in case of Descending Sort Order.

Difference between RANK() and DENSE_RANK()

The difference between the two is that RANK() leaves gaps while ranking the records whereas DENSE_RANK() doesn't leave any gaps. For example, if we have more than one records at a particular position then RANK() will place all those records in that position and it'll place the next record after a gap of the additional records. But, DENSE_RANK() (which will also place all the records in that position only) will not leave that gap for the next rank. Suppose, we have a tie at the third position and there are 2 records with the same value then RANK() and DENSE_RANK() both the functions will place both the records in the third position only, but RANK() will have the next record at the fifth position (leaving a gap of 1 position) whereas the DENSE_RANK() function will have the next record at the forth position itself (no gap).