RANK

Translation specification for the transformation of the RANK() function

Description

RANK sorts a result set and identifies the numeric rank of each row in the result. The only argument for RANK is the sort column or columns, and the function returns an integer that represents the rank of each row in the result. (RANK in Teradata)

Teradata syntax

RANK ( sort_expression [ ASC | DESC ] [,...] )

Snowflake syntax

RANK() OVER 
( 
    [ PARTITION BY <expr1> ]
    ORDER BY <expr2> [ { ASC | DESC } ] 
    [ <window_frame> ]
)

Sample Source Pattern

Setup data

Teradata

CREATE TABLE Sales (
  Product VARCHAR(255),
  Sales INT
);

INSERT INTO Sales (Product, Sales) VALUES ('A', 100);
INSERT INTO Sales (Product, Sales) VALUES ('B', 150);
INSERT INTO Sales (Product, Sales) VALUES ('C', 200);
INSERT INTO Sales (Product, Sales) VALUES ('D', 150);
INSERT INTO Sales (Product, Sales) VALUES ('E', 120);
INSERT INTO Sales (Product, Sales) VALUES ('F', NULL);

Snowflake

CREATE TABLE Sales (
  Product VARCHAR(255),
  Sales INT
);

INSERT INTO Sales (Product, Sales) VALUES ('A', 100);
INSERT INTO Sales (Product, Sales) VALUES ('B', 150);
INSERT INTO Sales (Product, Sales) VALUES ('C', 200);
INSERT INTO Sales (Product, Sales) VALUES ('D', 150);
INSERT INTO Sales (Product, Sales) VALUES ('E', 120);
INSERT INTO Sales (Product, Sales) VALUES ('F', NULL);

RANK() using ASC, DESC, and DEFAULT order

Teradata

Notice that Teradata's ordering default value when calling RANK() is DESC. However, the default in Snowflake is ASC. Thus, DESC is added in the conversion of RANK() when no order is specified.

SELECT
  Sales,
  RANK(Sales ASC) AS SalesAsc,
  RANK(Sales DESC) AS SalesDesc,
  RANK(Sales) AS SalesDefault
FROM
  Sales;

Snowflake

SELECT
  Sales,
  RANK() OVER (
  ORDER BY
    Sales ASC) AS SalesAsc,
  RANK() OVER (
  ORDER BY
    Sales DESC NULLS LAST) AS SalesDesc,
  RANK() OVER (
  ORDER BY
    Sales DESC NULLS LAST) AS SalesDefault
FROM
  Sales;
PreviousP_INTERSECTNextRegex functions

Last updated