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