Why is the subquery not working in Snowflake?-CodePudding

I am trying to run this query in Snowflake, but I keep getting an error.

select x.*, (select z.status from TBLA z where z.number_id=x.number_id and z.country=x.country and z.datetime=x.datetime) status
from
(
    select a.number_id, a.country, max(datetime) as datetime
    from TBLA a
    group by a.number_id, a.country
) x

This is the error I am getting:

SQL compilation error: Unsupported subquery type cannot be evaluated

Does anyone know how to fix this?

CodePudding user response：

To get the status for the latest datetime per number_id/country windowed function could be used:

SELECT a.*, 
  (ARRAY_AGG(a.status) WITHIN GROUP(ORDER BY a.datetime DESC) 
                       OVER(PARTITION BY a.number_id, a.country))[0] AS latest_status
FROM TBLA a;

CodePudding user response：

Looks like you are trying to get the latest status by number_id and country. A simple query to do that in Snowflake using window function row_number() is

select * from TBLA
qualify row_number() over (partition by number_id, country order by datetime desc) = 1;

CodePudding user response：

Only scalar subqueries are allowed in the SELECT. Your subquery is not inherently scalar.

https://docs.snowflake.com/en/user-guide/querying-subqueries.html