RANK

Window Function

Description

The RANK window function determines the rank of a value in a group of values, based on the ORDER BY expression in the OVER clause. If the optional PARTITION BY clause is present, the rankings are reset for each group of rows.

For more information, please refer to RANK function.

Grammar Syntax

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 RANK() OVER (ORDER BY bushels DESC) AS rank1,
RANK() OVER (PARTITION BY state ORDER BY bushels DESC) AS rank2,
RANK() OVER () AS rank3,
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
5	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 RANK() OVER (ORDER BY bushels DESC) AS rank1,
RANK() OVER (PARTITION BY state ORDER BY bushels DESC) AS rank2,
RANK()
    OVER (
    ORDER BY 1) AS rank3,
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
5	2	1	1

Related EWIs

There are no known issues.