Qualify group by in BigQuery

user_8248194 0
Viewed 1029

I've started using qualify in BigQuery and I have an issue with the group by operator.

This works correctly (although I have to put where true which is a bit weird):

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
  SELECT 'Kale', 23, 'Q2' UNION ALL
  SELECT 'Kale', 45, 'Q3' UNION ALL
  SELECT 'Kale', 3, 'Q4' UNION ALL
  SELECT 'Apple', 77, 'Q1' UNION ALL
  SELECT 'Apple', 0, 'Q2' UNION ALL
  SELECT 'Apple', 25, 'Q3' UNION ALL
  SELECT 'Apple', 2, 'Q4')
SELECT *
FROM Produce
WHERE true QUALIFY RANK() OVER (PARTITION BY product ORDER BY quarter  DESC) <= 1

But this doesn't work:

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
  SELECT 'Kale', 23, 'Q2' UNION ALL
  SELECT 'Kale', 45, 'Q3' UNION ALL
  SELECT 'Kale', 3, 'Q4' UNION ALL
  SELECT 'Apple', 77, 'Q1' UNION ALL
  SELECT 'Apple', 0, 'Q2' UNION ALL
  SELECT 'Apple', 25, 'Q3' UNION ALL
  SELECT 'Apple', 2, 'Q4')
SELECT product, count(*)
FROM Produce
WHERE true QUALIFY RANK() OVER (PARTITION BY product ORDER BY quarter  DESC) <= 1
group by 1

Am I doing something wrong? The error thrown is

Syntax error: Expected end of input but got keyword GROUP at [13:1]
google-bigquery teradata sql
2 Answers

You have the clauses in the wrong order. The ordering for what you care about is:

FROM
WHERE
GROUP BY
HAVING
QUALIFY

So, try this:

SELECT product, count(*)
FROM Produce
WHERE true 
GROUP BY 1
QUALIFY RANK() OVER (PARTITION BY product ORDER BY quarter DESC) <= 1
user_11440350

qualify is expected to be after group by:

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
  SELECT 'Kale', 23, 'Q2' UNION ALL
  SELECT 'Kale', 45, 'Q3' UNION ALL
  SELECT 'Kale', 3, 'Q4' UNION ALL
  SELECT 'Apple', 77, 'Q1' UNION ALL
  SELECT 'Apple', 0, 'Q2' UNION ALL
  SELECT 'Apple', 25, 'Q3' UNION ALL
  SELECT 'Apple', 2, 'Q4')
SELECT product, count(*)
FROM Produce
WHERE true 
group by product, quarter
QUALIFY RANK() OVER (PARTITION BY product ORDER BY quarter  DESC) <= 1
user_134735250
Related