DENSE_RANK

Window Function

Description

The DENSE_RANK window function determines the rank of a value in a group of values, based on the ORDER BY expression in the OVER clause. The DENSE_RANK function differs from RANK in one respect: if two or more rows tie, there is no gap in the sequence of ranked values.

For more information, please refer to DENSE_RANK function.

Grammar Syntax

DENSE_RANK() OVER
(
[ PARTITION BY expr_list ]
[ ORDER BY order_list ]
) 

This function is supported by Snowflake. However the ORDER BY clause is mandatory in Snowflake. In case it doesn't exist, an ORDER BY 1 will be added in order to ensure full equivalence.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE corn_production 
(
    farmer_ID INTEGER, 
    state varchar, 
    bushels float
);

INSERT INTO corn_production (farmer_ID, state, bushels) VALUES
    (1, 'Iowa', 100),
    (2, 'Iowa', 110),
    (3, 'Kansas', 120),
    (4, 'Kansas', 130),
    (5, 'Kansas', 110);

SELECT DENSE_RANK() OVER (ORDER BY bushels DESC) AS rank1,
DENSE_RANK() OVER (PARTITION BY state ORDER BY bushels DESC) AS rank2,
DENSE_RANK() OVER () AS rank3,
DENSE_RANK() OVER (PARTITION BY state) AS rank4
FROM corn_production;

Results

rank1	rank2	rank3	rank4
1	1	1	1
2	2	1	1
3	3	1	1
3	1	1	1
4	2	1	1

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE corn_production
(
    farmer_ID INTEGER,
    state varchar,
    bushels float
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/27/2024" }}';

INSERT INTO corn_production (farmer_ID, state, bushels) VALUES
    (1, 'Iowa', 100),
    (2, 'Iowa', 110),
    (3, 'Kansas', 120),
    (4, 'Kansas', 130),
    (5, 'Kansas', 110);

SELECT DENSE_RANK() OVER (ORDER BY bushels DESC) AS rank1,
DENSE_RANK() OVER (PARTITION BY state ORDER BY bushels DESC) AS rank2,
DENSE_RANK()
    OVER (
    ORDER BY 1) AS rank3,
DENSE_RANK()
    OVER (PARTITION BY state
    ORDER BY 1) AS rank4
FROM
    corn_production;

Results

rank1	rank2	rank3	rank4
1	1	1	1
2	2	1	1
3	3	1	1
3	1	1	1
4	2	1	1

Related EWIs

There are no known issues.