ROW_NUMBER

Description

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. (ROW_NUMBER in Transact-SQL).

Sample Source Pattern

Syntax

SQL Server

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )  

Snowflake SQL

Snowflake SQL complete documentation

ROW_NUMBER() OVER (
  [ PARTITION BY <expr1> [, <expr2> ... ] ]
  ORDER BY <expr3> [ , <expr4> ... ] [ { ASC | DESC } ]
  )

Examples

SQL Server

Code:

SELECT 
ROW_NUMBER() OVER(ORDER BY NAME  ASC) AS RowNumber, 
NAME
FROM HUMANRESOURCES.DEPARTMENT

Output:

RowNumber|NAME                      |
---------+--------------------------+
        1|Document Control          |
        2|Engineering               |
        3|Executive                 |
        4|Facilities and Maintenance|
        5|Finance                   |
        6|Human Resources           |
        7|Information Services      |
        8|Marketing                 |
        9|Production                |
       10|Production Control        |
       11|Purchasing                |
       12|Quality Assurance         |
       13|Research and Development  |
       14|Sales                     |
       15|Shipping and Receiving    |
       16|Tool Design               |

Snowflake SQL

Code:

SELECT ROW_NUMBER()
OVER(ORDER BY NAME  ASC) AS RowNumber,
NAME
FROM HUMANRESOURCES.DEPARTMENT;

Output:

RowNumber|NAME                      |
---------+--------------------------+
        1|Document Control          |
        2|Engineering               |
        3|Executive                 |
        4|Facilities and Maintenance|
        5|Finance                   |
        6|Human Resources           |
        7|Information Services      |
        8|Marketing                 |
        9|Production                |
       10|Production Control        |
       11|Purchasing                |
       12|Quality Assurance         |
       13|Research and Development  |
       14|Sales                     |
       15|Shipping and Receiving    |
       16|Tool Design               |