The difference between rank and dense rank in SQL
In SQL, there are two functions, rank and dense rank. I will illustrate the difference between them through an example. Let’s suppose that you have a table of employee ids and their salaries.
You can use these in combination with a partition. For example, you might want to order salaries by department. Suppose we get a list of the salaries (in descending order), partitioned by department. Normally rank will order the salaries in ascending order, so you have to reverse the rank to get them in descending order.
If we use rank ( ), we will obtain the rank of each salary. Salaries that are the same will be given the same rank, and the subsequent ranking will be skipped. For example, if there is a highest salary of $80,000 which is ranked 1, and two salaries of $60,000 as the next lowest salaries, these two will be ranked 2. Then rank number 3 will be skipped and the next highest salary (say $50,000) will be ranked 4.
On the other hand, dense_rank ( ) will give you the ordering of the salaries within the partition, but will not skip any ranks. So the second two highest salaries of $60,000 will be labelled 2, and the third highest salary will be given dense_rank 3.
Both of these functions are incredibly useful in SQL!
