ROW_NUMBER

Window Function

Description

The ROW_NUMBER window function assigns an ordinal number of the current row within a group of rows, counting from 1, based on the ORDER BY expression in the OVER clause. (RedShift SQL Language Reference ROW_NUMBER window function)

Grammar Syntax

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

This function is fully supported in Snowflake.

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.

For more information about quoted identifiers in functions, click here.

Sample Source Patterns

Setup data

Redshift

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

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT  ROW_NUMBER()  OVER ( ORDER BY bushels DESC) AS row1,
        ROW_NUMBER()  OVER ( PARTITION BY state ORDER BY bushels DESC) AS row2,
        ROW_NUMBER()  OVER () AS row3,
        ROW_NUMBER()  OVER ( PARTITION BY state) AS row4
FROM corn_production;

Results

ROW1	ROW2	ROW3	ROW4
1	1	1	1
2	2	2	2
4	3	3	3
3	1	4	1
5	2	5	2

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT  ROW_NUMBER()  OVER ( ORDER BY bushels DESC) AS row1,
        ROW_NUMBER()  OVER ( PARTITION BY state ORDER BY bushels DESC) AS row2,
        ROW_NUMBER()
        OVER (
        ORDER BY
      1) AS row3,
        ROW_NUMBER()
        OVER ( PARTITION BY state
        ORDER BY
      1) AS row4
FROM
        corn_production;

Results

ROW1	ROW2	ROW3	ROW4
1	1	1	1
2	2	2	2
4	3	3	3
3	1	4	1
5	2	5	2

Known Issues

No issues were found.

Related EWIs

There are no known issues.