BigQuery QUALITY clause available? Needing single output for nested query-CodePudding

Is the QUALIFY clause available in BQ? I'm trying to run the below query in an attempt to pull the single most popular Route value so that I can nest it in the WHERE clause of another query:

WITH table AS (
    SELECT 'Point A' as Origin, 'Point B' as Destination, A as Route
    FROM UNNEST(['Highway', 'Backroad', 'Highway', 'Highway', 'Backroad']) as A
)


SELECT
    DISTINCT Route
FROM
   table
WHERE 
    Origin = 'Point A' AND
    Destination = 'Point B'
QUALIFY RANK() OVER (PARTITION BY Origin, Destination ORDER BY COUNT(*) DESC) = 1

What I have

What I want (most commonly used route)

And getting this generic error:

Error running query An internal error occurred and the request could not be completed. This is usually caused by a transient issue. Retrying the job with back-off as described in the BigQuery SLA should solve the problem: https://cloud.google.com/bigquery/sla. If the error continues to occur please contact support at https://cloud.google.com/support. Error: 80038528

If there is a better way of doing this, please let me know. I need an efficient way of pulling a single row and column after evaluating the most popular value, ideally without nesting queries.

Here is the BQ QUALIFY clause documentation.

CodePudding user response：

Consider below approach

SELECT
  DISTINCT Route
FROM
  table
WHERE 
  Origin = 'Point A' AND
  Destination = 'Point B'
GROUP BY 
  Origin, Destination, Route 
QUALIFY 
  RANK() OVER (PARTITION BY Origin, Destination ORDER BY COUNT(*) DESC) = 1