# DENSE\_RANK

{% hint style="info" %}
Some parts in the output code are omitted for clarity reasons.
{% endhint %}

## Description

This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row. ([DENSE\_RANK in Transact-SQL](https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-ver15)).

## Sample Source Pattern

### Syntax

{% tabs %}
{% tab title="SQL Server" %}
{% code title="IN -> SqlServer_01.sql" %}
```sql
DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )  
```
{% endcode %}
{% endtab %}

{% tab title="Snowflake SQL" %}
[Snowflake SQL Documentation](https://docs.snowflake.com/en/sql-reference/functions/dense_rank.html)

{% code title="OUT -> SqlServer_01.sql" %}
```sql
DENSE_RANK( )
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE 1 COLUMN 15 OF THE SOURCE CODE STARTING AT 'OVER'. EXPECTED 'BATCH' GRAMMAR. **
--              OVER ( [ <partition_by_clause> ] < order_by_clause > )
```
{% endcode %}
{% endtab %}
{% endtabs %}

## Examples

{% tabs %}
{% tab title="SQL Server" %}
**Code:**

{% code title="IN -> SqlServer_02.sql" %}
```sql
SELECT TOP 10 BUSINESSENTITYID, NATIONALIDNUMBER, RANK() OVER (ORDER BY NATIONALIDNUMBER) AS RANK FROM HUMANRESOURCES.EMPLOYEE AS TOTAL
```
{% endcode %}



**Result:** &#x20;

```
BUSINESSENTITYID|NATIONALIDNUMBER|DENSE_RANK|
----------------+----------------+----------+
              57|10708100        |         1|
              54|109272464       |         2|
             273|112432117       |         3|
               4|112457891       |         4|
             139|113393530       |         5|
             109|113695504       |         6|
             249|121491555       |         7|
             132|1300049         |         8|
             214|131471224       |         9|
              51|132674823       |        10|
```
{% endtab %}

{% tab title="Snowflake SQL" %}
**Code:**

{% code title="OUT -> SqlServer_02.sql" %}
```sql
SELECT TOP 10
BUSINESSENTITYID,
NATIONALIDNUMBER,
RANK() OVER (ORDER BY NATIONALIDNUMBER) AS RANK
FROM
HUMANRESOURCES.EMPLOYEE AS TOTAL;
```
{% endcode %}



**Result:** &#x20;

```
BUSINESSENTITYID|NATIONALIDNUMBER|DENSE_RANK|
----------------+----------------+----------+
              57|10708100        |         1|
              54|109272464       |         2|
             273|112432117       |         3|
               4|112457891       |         4|
             139|113393530       |         5|
             109|113695504       |         6|
             249|121491555       |         7|
             132|1300049         |         8|
             214|131471224       |         9|
              51|132674823       |        10|
```
{% endtab %}
{% endtabs %}

### Related EWIs

* [SSC-EWI-0001](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/conversion-issues/general/ssc-ewi-0001): Unrecognized token on the line of the source code.
