DENSE_RANK

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Applies to

• SQL Server

• Azure Synapse Analytics

Some parts in the output code are omitted for clarity reasons.

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).

Sample Source Pattern

Syntax

SQL Server

IN -> SqlServer_01.sql

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )  

Snowflake SQL

Examples

SQL Server

Code:

IN -> SqlServer_02.sql

SELECT TOP 10 BUSINESSENTITYID, NATIONALIDNUMBER, RANK() OVER (ORDER BY NATIONALIDNUMBER) AS RANK FROM HUMANRESOURCES.EMPLOYEE AS TOTAL

Result:

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|

Snowflake SQL

Related EWIs

• SSC-EWI-0001: Unrecognized token on the line of the source code.