ROW_NUMBER

Applies to

• SQL Server

• Azure Synapse Analytics

Some parts in the output code are omitted for clarity reasons.

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

IN -> SqlServer_01.sql

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

Snowflake SQL

Snowflake SQL complete documentation

OUT -> SqlServer_01.sql

ROW_NUMBER( )
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '2' COLUMN '5' OF THE SOURCE CODE STARTING AT 'OVER'. EXPECTED 'BATCH' GRAMMAR. CODE '80'. **
--    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Examples

SQL Server

Code:

IN -> SqlServer_02.sql

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:

OUT -> SqlServer_02.sql

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               |

Related EWIs

• SSC-EWI-0001: Unrecognized token on the line of the source code.