RANK

Description

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. (RANK in Transact-SQL).

Sample Source Pattern

Syntax

SQL Server

IN -> SqlServer_01.sql

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )  

Snowflake SQL

Snowflake SQL Documentation

OUT -> SqlServer_01.sql

RANK( )
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE 1 COLUMN 9 OF THE SOURCE CODE STARTING AT 'OVER'. EXPECTED 'BATCH' GRAMMAR. **
--        OVER ( [ partition_by_clause ] order_by_clause )

Examples

SQL Server

Code:

IN -> SqlServer_02.sql

SELECT TOP 10 BUSINESSENTITYID, NATIONALIDNUMBER, RANK() OVER (ORDER BY NATIONALIDNUMBER) AS RANK FROM HUMANRESOURCES.EMPLOYEE AS TOTAL

Result:

BUSINESSENTITYID|NATIONALIDNUMBER|RANK|
----------------+----------------+----+
              57|10708100        |   1|
              54|109272464       |   2|
             273|112432117       |   3|
               4|112457891       |   4|
             139|113393530       |   5|
             109|113695504       |   6|
             249|121491555       |   7|
             132|1300049         |   8|
             214|131471224       |   9|
              51|132674823       |  10|

Snowflake SQL

Code:

OUT -> SqlServer_02.sql

SELECT TOP 10
BUSINESSENTITYID,
NATIONALIDNUMBER,
RANK() OVER (ORDER BY NATIONALIDNUMBER) AS RANK
FROM
HUMANRESOURCES.EMPLOYEE AS TOTAL;

Result:

BUSINESSENTITYID|NATIONALIDNUMBER|RANK|
----------------+----------------+----+
              57|10708100        |   1|
              54|109272464       |   2|
             273|112432117       |   3|
               4|112457891       |   4|
             139|113393530       |   5|
             109|113695504       |   6|
             249|121491555       |   7|
             132|1300049         |   8|
             214|131471224       |   9|
              51|132674823       |  10|

Related EWIs

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