Qualify equivalent clause in BigQuery Standard SQL

Question

Is there any equivalent 'Qualify' clause of Teradata in BigQuery Standard SQ?

I need it because we cant use analytic functions in Where/Having clause.

What about pushing your analytic function down to a sub/nested select and filtering on top of it? — Graham Polley
I don't believe it exists. I could be wrong, but I don't see anything in the docs. — Graham Polley
QUALIFY appears to be a Teradata-specific extension to the SQL standard that isn't implemented by any other SQL engines. — Elliott Brossard
@ElliottBrossard Good to know that Snowflake supports it too :) - Why no windowed functions in where clauses? — Lukasz Szozda

Alejandro Henríquez Lazo Alejandro Henríquez Lazo · Accepted Answer · 2020-06-17T22:31:46

The equivalent query is:

SELECT * EXCEPT (_row_number)
FROM  (SELECT {{ COLUMNS }}
              , ROW_NUMBER() {{ RULE }} AS _row_number
       FROM   {{ MY_TABLE }}) AS t
WHERE  _row_number = 1

Qualify equivalent clause in BigQuery Standard SQL

2 Answers