29 Jan 21 ·  Cpaul in Guides ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

Convert Teradata Qualify Clause to BigQuery

Teradata Qualify Clause helps filter the rows that qualify the window function

Example:

SELECT a, b
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) = 1

You will need to rewrite to the below format in BigQuery:

SELECT a, b
FROM (
SELECT a, b,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) row_num
FROM t1
) WHERE row_num = 1

NOTE: The Qualify clause is not supported by Roboquery conversion tool. This function has to be handled manually