---
title: "SQL Window Functions: RANK vs DENSE_RANK Top Usage"
description: "SQL Rank function vs Dense_rank explained the usage with real examples"
url: https://srinimf.com/2016/09/13/how-to-write-great-sql-for-rank-and-denserank/
date: 2016-09-13
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2016/09/ranks-vs-dense-rank.jpg?fit=1200%2C900&ssl=1
categories: ["DB2", "SQL"]
tags: ["Best SQL examples for RANK and DENSE_RANK"]
type: post
lang: en
---

# SQL Window Functions: RANK vs DENSE_RANK Top Usage

## **Rank Function** 

Returns a rank number for each row value. Use this specification if you want rank numbers to be skipped when duplicate row values exist. For example, suppose the top five finishers in a marathon have the following times:

```
2:31:57
2:34:52
2:34:52
2:37:26
2:38:01
2:37:26
```

**Related:** [Best SQL Course for Beginner to Professional ](http://click.linksynergy.com/fs-bin/click?id=yYOcxCqEbGo&subid=&offerid=323058.1&type=10&tmpid=14538&RD_PARM1=https%3A%2F%2Fwww.udemy.com%2Fthe-complete-sql-bootcamp%2F)

Table 1. Example of values returned when you specify RANK. Rank 3 was skipped, and it was assigned rank 5.

| Value | Rank number |
| --- | --- |
| 2:31:57 | 1 |
| 2:34:52 | 2 |
| 2:34:52 | 2 |
| 2:37:26 | 4 |
| 2:38:01 | 5 |

Data for RANK function:

```
DATA
-------
100
 35
 23
  8
  8
  6
```

Suppose that you use the following RANK specification:

```
SELECT DATA,
  RANK() OVER (ORDER BY DATA DESC) AS RANK_DATA
  FROM T1
  ORDER BY RANK_DATA;
```

Related[How to Rank the Output Using Over Partition By DB2 SQL](https://srinimf.com/2014/09/06/db2-partition-by-sql-query/)

SQL returns the following ranked data:

```
DATA                  RANK_DATA
----------                 ---------
 100                1
  35                2
  23                3
   8                4
   8                4
   6                6
```

In the above example, with the RANK function, two equal values are both ranked as 4. The next rank number is 6. Number 5 is skipped.

## **DENSE_RANK Function**

Returns a rank number for each row value. Use this specification if you do not want rank numbers to be skipped when duplicate row values exist. For example, when you specify DENSE_RANK with the same times that are listed in the description of RANK, DB2 returns the following rank numbers:

Table 2. Example of values returned when you specify DENSE_RANK

Related[DB2 SQL — RANK Vs DENSE_RANK: Top Differences](https://srinimf.com/2024/01/01/db2-sql-rank-vs-dense_rank-top-differences/)

| **Value** | **Rank number** |
| --- | --- |
| 2:31:57 | 1 |
| 2:34:52 | 2 |
| 2:34:52 | 2 |
| 2:37:26 | 3 |
| 2:38:01 | 4 |

[SQL Query](https://srinimf.com/2010/07/04/db2-row-number/) for DENSE_RANK

Suppose that you use the following DENSE_RANK specification on the same data:

```
SELECT DATA,
  DENSE_RANK() OVER (ORDER BY DATA DESC) AS RANK_DATA
  FROM T1
  ORDER BY RANK_DATA;
```

SQL returns the following ranked data:

```
DATA                  RANK_DATA
---------           ----------
 100                1
  36                2
  23                3
   8                4
   8                4
   6                5
```

In the above example with the DENSE_RANK option, those two equal values are also ranked as 4. However, the next rank number is 5. With DENSE_RANK, no gaps exist in the sequential rank numbering.

**Summary of RANK and DENSE_RANK**

The recommendation is to learn SQL from a good online course provider. Then your knowledge of writing SQL queries will improve much better. If you are interested you can try [learning SQL](http://click.linksynergy.com/fs-bin/click?id=yYOcxCqEbGo&offerid=323058.960&subid=0&type=4) here.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2016%2F09%2F13%2Fhow-to-write-great-sql-for-rank-and-denserank%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2016%2F09%2Franks-vs-dense-rank.jpg%3Ffit%3D1200%252C900%26ssl%3D1&description=SQL%20Window%20Functions%3A%20RANK%20vs%20DENSE_RANK%20Top%20Usage)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2016/09/13/how-to-write-great-sql-for-rank-and-denserank/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2016/09/13/how-to-write-great-sql-for-rank-and-denserank/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2016/09/13/how-to-write-great-sql-for-rank-and-denserank/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2016/09/13/how-to-write-great-sql-for-rank-and-denserank/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20SQL%20Window%20Functions%3A%20RANK%20vs%20DENSE_RANK%20Top%20Usage&body=https%3A%2F%2Fsrinimf.com%2F2016%2F09%2F13%2Fhow-to-write-great-sql-for-rank-and-denserank%2F&share=email)
-

### Like this:

Like Loading…