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.
Asked
Active
Viewed 4,518 times
2
-
1What about pushing your analytic function down to a sub/nested select and filtering on top of it? – Graham Polley Jun 06 '17 at 12:34
-
Yes I can do that but I am expecting some kind of a one line solution like the way it happens in Teradata – abhishek jha Jun 06 '17 at 12:36
-
1I don't believe it exists. I could be wrong, but I don't see anything in the docs. – Graham Polley Jun 06 '17 at 12:37
-
1you can submit feature request for this! – Mikhail Berlyant Jun 06 '17 at 13:13
-
3`QUALIFY` appears to be a Teradata-specific extension to the SQL standard that isn't implemented by any other SQL engines. – Elliott Brossard Jun 06 '17 at 17:04
-
3@ElliottBrossard Good to know that Snowflake supports it too :) - [Why no windowed functions in where clauses?](https://stackoverflow.com/questions/13997177/why-no-windowed-functions-in-where-clauses) – Lukasz Szozda Nov 17 '19 at 20:22
-
2QUALIFY is now suported by BQ. https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause – tom10271 May 17 '22 at 03:00
2 Answers
4
It has since been released (was pre-GA at the time of writing, now GA as pointed out by Ran below):
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause
gaatjeniksaan
- 1,412
- 2
- 12
- 17
3
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
