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

Query

IN -> Teradata_01.sql

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

Query

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE Sales (
  Product VARCHAR(255),
  Sales INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

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.

Query

IN -> Teradata_02.sql

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

Result

SALES	SALESASC	SALESDESC	SALESDEFAULT
NULL	6	6	6
200	5	1	1
150	3	2	2
150	3	2	2
120	2	4	4
100	1	5	5

Snowflake

Query

OUT -> Teradata_02.sql

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;

Result

SALES	SALESASC	SALESDESC	SALESDEFAULT
NULL	6	6	6
200	5	1	1
150	3	2	2
150	3	2	2
120	2	4	4
100	1	5	5

Known Issues

No issues were found.

Related EWIs

No related EWIs.