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;
Last updated
Was this helpful?