ROW_NUMBER(), RANK(), and DENSE_RANK() Functions in SQL.

Fig1 -> Difference between Row_number, Rank, and Dense Rank.

In this article we will go through all the three ranking functions in SQL and how do they differ from each other.

All three functions in SQL are also known as window functions AND are widely used to rank the rows in the result set.

Let's check out these ranking functions one by one with their use-case.

ROW_NUMBER(): It is a ranking function that assigns a unique number to each row to which it is applied (Rows can be in the partition or all the rows from the result set). It always assigns the 1 to the first row and assigns the next number to the next rows.

When we have multiple rows having the same value on which ranking or order needs to be done then in that case row_number randomly assigns the ranks to these rows.

The basic syntax of the row_number function

ROW_NUMBER() OVER (
PARTITION BY COL1,COL2
ORDER BY COL1 DESC|ASC)

In this syntax,

• First, the PARTITION BY clause divides the result set returned from the FROM clause into partitions. The PARTITION BY clause is optional. If you omit it, the whole result set is treated as a single partition.
• Then, the ORDER BY clause sorts the rows in each partition. Because the ROW_NUMBER() is an…