When to choose rank() over dense_rank() or row_number()

When applying either RANK or DENSE_RANK to a column which has no ties, they would both result in the same series which would be generated by ROW_NUMBER. The difference between RANK and DENSE_RANK appears in the presence of ties, and is subtle. Consider the following table, along with row number, rank, and dense rank values:

SALARY | ROW_NUMBER | RANK | DENSE_RANK
1000   | 1          | 1    | 1
1500   | 2          | 2    | 2
1500   | 3          | 2    | 2
2000   | 4          | 4    | 3
2200   | 5          | 5    | 4
2500   | 6          | 6    | 5
2500   | 7          | 6    | 5
2500   | 8          | 6    | 5
3000   | 9          | 9    | 6

Hopefully you can see above that when a tie of two or more records appears, both RANK and DENSE_RANK assign the same rank to all records with the same value. However, where they differ is that RANK continues the rank count in line with the ROW_NUMBER series, while DENSE_RANK does not, and instead continues the rank count with the next value after the duplicate rank.

Now getting to your question, whether you choose RANK or DENSE_RANK depends on your requirements. For example, if you were reporting winners, and always needed to report a first, second, and third place, regardless of ties for each place, you would use DENSE_RANK. Otherwise, you would use RANK, which might mean that there is no second or third place. If you were certain that there could never be duplicates, then you could just use ROW_NUMBER.

The below example should help illustrate the difference. (DB fiddle link of the example below - https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=cef0a4da7559de657aae332491a9c500)

CREATE TABLE t AS
SELECT 'p' v FROM dual UNION ALL
SELECT 'p'   FROM dual UNION ALL
SELECT 'p'   FROM dual UNION ALL
SELECT 'q'   FROM dual UNION ALL
SELECT 'r'   FROM dual UNION ALL
SELECT 'r'   FROM dual UNION ALL
SELECT 's'   FROM dual UNION ALL
SELECT 't'   FROM dual;

SELECT
  v,
  ROW_NUMBER() OVER (ORDER BY v) row_number,
  RANK()       OVER (ORDER BY v) rank,
  DENSE_RANK() OVER (ORDER BY v) dense_rank
FROM t
ORDER BY v;
The above will yield:

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| p |          1 |    1 |          1 |
| p |          2 |    1 |          1 |
| p |          3 |    1 |          1 |
| q |          4 |    4 |          2 |
| r |          5 |    5 |          3 |
| r |          6 |    5 |          3 |
| s |          7 |    7 |          4 |
| t |          8 |    8 |          5 |
+---+------------+------+------------+

Ties are assigned the same rank for both RANK and DENSE_RANK however in RANK after the tie you will have ranks with the next ranking(s) skipped. The last part marked in Bold is the only difference between RANK and DENSE_RANK. In DENSE_RANK the numbers would not be skipped.

A good analogy would be imagine in your class the highest overall marks was obtained by 2 students. So, the 1st position is tied. You have to give the first prize to the both of them. Now, the student whose total is just after the highest mark what position do you assign to him/her. If you ask RANK() function - it will tell you 3rd since 1st position is occupied by 2 people. But if you ask DENSE_RANK() function - it will tell you 2nd position as it does not skip any ranking & thus in this case 3rd position will be awarded to another person whose overall marks is just after the person on 2nd position. While if your problem is that you have only one prize for the 1st position only one winner :-) then ROW_NUMBER will solve it for you as ROW_NUMNER() will assign 1st and 2nd position randomly to the students who got highest mark (something like a lottery) and so there remains no contradiction on the 3rd position in this case.

Now coming to Use-case for RANK : I know for a fact that lot of qualifier examinations use RANK() methodology (not necessarily the rank function in database but the same algorithm or methodology) to short-list the number of students since the competition can be very close sometimes so ties are not that rare. However, the problem is that - there is a certain rank let’s say 50 that is used as a cut-off but at the same time they have a fixed number of seats which cannot be exceeded. So, in case of tie, you have to award same rank to both students as there isn’t much you can do if both score the same marks however the next ranking gets skipped otherwise seats would be overfilled. It is similar to lets say a soccer league where at the end of league stage you have multiple teams with same number of points. Now in this kind of league you can use other criterions such as number of goals etc to eliminate the tie for playoffs but in case of examination you certainly cannot do re-examination to eliminate ties. This is a very practical use-case to illustrate where the concept can sometimes be useful.

Implementation to find duplicates is easier using rank() over dense_rank since all you have to check is when the line number increases by more than 1.

Additional code is needed if using dense_rank.

Interestingly, you could use row_number() over (partition by list of every column you want to check for duplicates order by list of every column you want to check for duplicates')

But I would only recommend using row_number instead of rank() if you do not want to check the entire row.