Subquery of type IN must have only one output column at [4:3]

Question

I have this query:

SELECT *
FROM `tlp-dataplatform-prod.published_kpi.UCV*`
WHERE (customer_code, _TABLE_SUFFIX) IN (
  SELECT customer_code, MAX(_TABLE_SUFFIX) AS max_suffix
  FROM `tlp-dataplatform-prod.published_kpi.UCV*`
  GROUP BY 1
)

this query uses a subquery to first find the maximum _TABLE_SUFFIX for each customer_code using the MAX() function and the GROUP BY clause. The outer query then filters the results to only include the rows where the (customer_code, _TABLE_SUFFIX) pair matches the values returned by the subquery. This effectively returns only the rows with the maximum _TABLE_SUFFIX for each customer_code, while still selecting all columns from the table.

But it returns me this error on BigQuery: "Subquery of type IN must have only one output column at [4:3]"

I tried to change the order of the in but nothing cahnge

Answer 1

You can use this query instead:

SELECT *
FROM `tlp-dataplatform-prod.published_kpi.UCV*` as u1
WHERE  _TABLE_SUFFIX = (
  SELECT MAX(_TABLE_SUFFIX) 
  FROM `tlp-dataplatform-prod.published_kpi.UCV*` as u2
  WHERE u2.customer_code = u1.customer_code
)

It checks if _TABLE_SUFFIX is maximum for corresponding customer_code.