title: BigQuery QUALITY clause available? Needing single output for nested query
body: <p>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:</p>
  <pre><code>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
  </code></pre>
  <p><a href="https://i.stack.imgur.com/XLp7H.png" rel="nofollow noreferrer">What I have</a></p>
  <p><a href="https://i.stack.imgur.com/LrUuZ.png" rel="nofollow noreferrer">What I want (most commonly used route)</a></p>
  <p>And getting this generic error:</p>
  <blockquote>
  <p>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: <a href="https://cloud.google.com/bigquery/sla" rel="nofollow noreferrer">https://cloud.google.com/bigquery/sla</a>. If the error
  continues to occur please contact support at
  <a href="https://cloud.google.com/support" rel="nofollow noreferrer">https://cloud.google.com/support</a>. Error: 80038528</p>
  </blockquote>
  <p>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.</p>
  <p><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause" rel="nofollow noreferrer">Here is the BQ QUALIFY clause documentation.</a></p>