> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# DENSE_RANK()

## Overview

The `DENSE_RANK()` window function assigns a rank for each value within a specified group, based on the `ORDER BY` expression in the `OVER` clause.  Unlike the `RANK()` function, which can leave gaps in the ranking sequence when there are ties, `DENSE_RANK()` provides consecutive rank values without any gaps. This function can be used with all <a href="/sql-reference/sql-data-types/overview" target="_blank">data types supported by Oxla</a>.

## Syntax

The syntax for this function is as follows:

```sql theme={null}
DENSE_RANK() OVER (
  [PARTITION BY partition_expression]
  ORDER BY sort_expression
)
```

The output type for this function is a `BIGINT` and it indicates the rank of values in a table, regardless of the input types. If the `ORDER BY` expression is omitted, all ranks will default to 1. In case an optional `PARTITION BY` expression is included, the rankings are reset for each group of rows. The rows with equal values for the ranking criteria receive the same rank.

<Info>Unlike `RANK()` function, there is no gap in the sequence of ranked values (if two rows are ranked 1, the next rank will be 2)</Info>

## Parameters

* `()`: this function takes no parameters, but empty parentheses is required
* `PARTITION BY`: optional clause, which is used to divide the result set into partitions to which the `DENSE_RANK()` function is applied (if omitted, the entire result set is treated as a single partition)
* `ORDER BY`: order of rows in each partition to which the function is applied

## Examples

For the needs of this section, we will create a `winsales` table that stores information about some sales transactions:

```sql theme={null}
CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid text,
  qty int,
  qty_shipped int);
INSERT INTO winsales VALUES
    (30001, '8/2/2003', 3, 'b', 10, 10),
    (10001, '12/24/2003', 1, 'c', 10, 10),
    (10005, '12/24/2003', 1, 'a', 30, null),
    (40001, '1/9/2004', 4, 'a', 40, null),
    (10006, '1/18/2004', 1, 'c', 10, null),
    (20001, '2/12/2004', 2, 'b', 20, 20),
    (40005, '2/12/2004', 4, 'a', 10, 10),
    (20002, '2/16/2004', 2, 'c', 20, 20),
    (30003, '4/18/2004', 3, 'b', 15, null),
    (30004, '4/18/2004', 3, 'b', 20, null),
    (30007, '9/7/2004', 3, 'c', 30, null);	 
```

### DENSE\_RANK() with ORDER BY

In this example we will focus on executing the `DENSE_RANK()` function with `ORDER BY` keyword and calculate the descending dense rank of all rows based on the quantity sold:

```sql theme={null}
SELECT salesid, qty
  Dense_RANK() OVER (ORDER BY qty DESC) AS d_rnk
  RANK() OVER (ORDER BY qty DESC) AS rnk
FROM winsales
ORDER BY 2,1;
```

Here is the output for the query presented above that includes the sales ID along with the quantity sold
and both dense and regular ranks:

```sql theme={null}
  salesid | qty | d_rnk | rnk 
---------+-----+-------+-----
   10001 |  10 |     5 |   8
   10006 |  10 |     5 |   8
   30001 |  10 |     5 |   8
   40005 |  10 |     5 |   8
   30003 |  15 |     4 |   7
   20001 |  20 |     3 |   4
   20002 |  20 |     3 |   4
   30004 |  20 |     3 |   4
   10005 |  30 |     2 |   2
   30007 |  30 |     2 |   2
   40001 |  40 |     1 |   1
(11 rows)
```

### DENSE\_RANK() with ORDER BY and PARTITION\_BY

In this example we will focus on executing the `DENSE_RANK()` function with `ORDER BY` keyword and `PARTITION BY` clause and partition the table by seller ID, then order each partition by the quantity and assign a dense rank to each row:

```sql theme={null}
SELECT salesid, sellerid, qty
  DENSE_RANK() OVER (PARTITION BY sellerid ORDER BY qty DESC) AS d_rnk
FROM winsales
ORDER BY 2,3,1;
```

Here is the output for the query presented above:

```sql theme={null}
 salesid | sellerid | qty | d_rnk 
---------+----------+-----+-------
   10001 |        1 |  10 |     2
   10006 |        1 |  10 |     2
   10005 |        1 |  30 |     1
   20001 |        2 |  20 |     1
   20002 |        2 |  20 |     1
   30001 |        3 |  10 |     4
   30003 |        3 |  15 |     3
   30004 |        3 |  20 |     2
   30007 |        3 |  30 |     1
   40005 |        4 |  10 |     2
   40001 |        4 |  40 |     1
(11 rows)
```