20 Oct 18 · Atori ·       Add to Favorites  

An equivalent of "QUALIFY" command in Snowflake

Teradata supports QUALIFY function that offers great flexibility to filter only the rows that pass the ranking or row_number. However, when you are migrating to Snowflake, the qualify function is missing in Snowflake

Teradata Qualify Command:

SELECT c1
FROM t1
WHERE c1='A'
QUALIFY ROW_NUMBER() OVER
(PARTITION by c1 ORDER BY c1) = 1;

Snowflake equivalent for Qualify Command:

SELECT * FROM
( SELECT c1, ROW_NUMBER() OVER (PARTITION by c1 ORDER BY c1) rn FROM t1 WHERE c1='A' ) WHERE rn = 1;

Atori

posted on 20 Oct 18

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied