View Categories

What is the use of RANK() and DENSE_RANK() functions?

Parthiban Kannan
Updated on December 2, 2025

1 min read

The RANK() and DENSE_RANK() functions in SQL are both window functions used to assign rankings to rows based on a specific ordering — but they differ in how they handle ties (rows with equal values).

Let’s say we have a table Sales with columns EmployeeID and Revenue, and we want to rank employees based on their revenue in descending order.

🗃️
filename.sql 
SELECT 
   EmployeeID,
   Revenue,
   RANK() OVER (ORDER BY Revenue DESC) AS RankPosition,
   DENSE_RANK() OVER (ORDER BY Revenue DESC) AS DenseRankPosition
FROM Sales;

Here’s how they behave:

  • RANK() gives the same rank to employees with the same revenue, but skips the next rank number.
  • DENSE_RANK() also gives the same rank to ties, but does not skip the next rank number.

For example, if the revenues are:

🗃️
filename.sql 
Revenue | RANK | DENSE_RANK
-------- | ---- | ----------
10000    | 1    | 1
9500     | 2    | 2
9500     | 2    | 2
9000     | 4    | 3

Notice how after rank 2, RANK() jumps to 4, while DENSE_RANK() continues with 3.

I applied this in a sales leaderboard report where we displayed sales reps based on their monthly performance. We used RANK() when the goal was to show actual position gaps (like in a competition), and DENSE_RANK() when we wanted continuous numbering without skipping ranks for ties.

One challenge I faced was when users got confused seeing rank gaps — for example, there’s no rank 3 in the RANK() output when two people tie at rank 2. To solve that, I explained and switched to DENSE_RANK() for user-facing reports.

A limitation is that these functions only work with ORDER BY inside OVER(), so you can’t apply them without defining how to rank.

An alternative, if you only need unique sequential numbering regardless of ties, is to use ROW_NUMBER(), which assigns a distinct number to every row.

Scroll to Top