Row Number

Window Function

Description

Returns the number of the current row within its partition, counting from 1.

Click here to navigate to the PostgreSQL docs page for this syntax.

The function Row_Number() is supported on Snowflake.

Grammar Syntax

ROW_NUMBER() OVER(
    [PARTITION BY column_1, column_2,…]
    [ORDER BY column_3,column_4,…]
)

This syntax is fully supported by Snowflake except that in PostgreSQL the order by clause is optional and in Snowflake this is not allowed. In case the order by clause is not present in the function the clause will be added.

Sample Source Patterns

This example will be used for the following code samples

CREATE OR REPLACE TABLE Music (
    music_id SERIAL PRIMARY KEY,
    music_genre VARCHAR (40) NOT NULL
);

INSERT INTO Music (music_genre)
VALUES
    ('Country'),
    ('Blues'),
    ('Hip Hop');

CREATE OR REPLACE TABLE Singers (
    singer_id SERIAL PRIMARY KEY,
    singer_name VARCHAR (255) NOT NULL,
    music_id INT NOT NULL,
    FOREIGN KEY (music_id) REFERENCES Music (music_id)
);

INSERT INTO Singers(singer_name, music_id)
VALUES
    ('Robert Johnson', 2),
    ('Johnny Cash', 1),
    ('Bessie Smith', 2),
    ('2pac', 3),
    ('Jay-Z', 3),
    ('Dolly Parton', 1);

Sample with Order By Clause

PostgreSQL

Code

SELECT
    singer_id,
    singer_name,
    music_id,
    ROW_NUMBER () OVER (ORDER BY singer_name)
FROM
    Singers;

Result

singer_id | singer_name    | music_id  | row_number  |
----------+----------------+-----------+-------------+
 4        | 2pac           | 3         | 1           |
 3        | Bessie Smith   | 2         | 2           |
 6        | Dolly Parton   | 1         | 3           |
 5        | Jay-Z          | 3         | 4           |
 2        | Johnny Cash    | 1         | 5           |
 1        | Robert Johnson | 2         | 6           |

Snowflake

Code

SELECT
    singer_id,
    singer_name,
    music_id,
    ROW_NUMBER () OVER (ORDER BY singer_name)
FROM
    Singers;

Result

singer_id | singer_name    | music_id  | row_number  |
----------+----------------+-----------+-------------+
 4        | 2pac           | 3         | 1           |
 3        | Bessie Smith   | 2         | 2           |
 6        | Dolly Parton   | 1         | 3           |
 5        | Jay-Z          | 3         | 4           |
 2        | Johnny Cash    | 1         | 5           |
 1        | Robert Johnson | 2         | 6           |

Sample without Order By Clause

PostgreSQL

Code

SELECT
    singer_id,
    singer_name,
    music_id,
    ROW_NUMBER () OVER ()
FROM
    Singers;

Result

singer_id | singer_name    | music_id  | row_number  |
----------+----------------+-----------+-------------+
 1        | Robert Johnson | 2         | 1           |
 2        | Johnny Cash    | 1         | 2           |
 3        | Bessie Smith   | 2         | 3           |
 4        | 2pac           | 3         | 4           |
 5        | Jay-Z          | 3         | 5           |
 6        | Dolly Parton   | 1         | 6           |

Snowflake

Code

SELECT
    singer_id,
    singer_name,
    music_id,
    ROW_NUMBER () OVER (ORDER BY 1)
FROM
    Singers;

Result

singer_id | singer_name    | music_id  | row_number  |
----------+----------------+-----------+-------------+
 1        | Robert Johnson | 2         | 1           |
 2        | Johnny Cash    | 1         | 2           |
 3        | Bessie Smith   | 2         | 3           |
 4        | 2pac           | 3         | 4           |
 5        | Jay-Z          | 3         | 5           |
 6        | Dolly Parton   | 1         | 6           |