Rank Dense Rank Row Number

The RANK() function assigns each row a rank according to an ascending or descending order. If there are matching values, it assigns them the same position and then skips the next number for the next rank. For example, if two rows have equivalent values and are both assigned rank 1, the next rank would be 3 instead of 2.

Let's use the Sales table from the previous question to illustrate the RANK() function. The query to rank in order of the amount looks like this:

SELECT
  id,
  day,
  amount,
  RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM
  sales;

The result is shown in the image below. You will observe that the amount 900 takes the first rank and 200 the lowest rank. Also, there is a gap between rank 2 and 4 because two values have the same rank. You can also infer that the most sales were on Friday and the least on Monday.

DENSE_RANK() function is similar to RANK() in that it assigns ranks to rows, but the difference is that DENSE_RANK does not leave a gap when there are two or more equivalent values. Let's illustrate it with the Sales table from above. The query is shown below.

SELECT
  id,
  day,
  amount,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM
  sales;

The result is shown below. As you will notice, there is no gap between the ranks like in the RANK function.

ROW_NUMBER assigns a unique number to each row depending on the order you specify. It does not skip numbers; even though there are equivalent values, it assigns them different numbers, unlike RANK and DENSE_RANK functions that give them the same rank.

Let's use the same Sales table to illustrate. The query below shows how to use the ROW_NUMBER function.

SELECT
  id,
  day,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rowNumber
FROM
  sales;

The result is shown in the image below. You will notice that the rownumber column increases, and even though there are matching values, it just assigns a unique row number to each.