Get any not null value of other fileds in aggregations

Question

I want to aggregate on some fields and get any not null value on others. To be more precise the query looks something like:

SELECT id, any_value(field1), any_value(field2) FROM mytable GROUP BY ID

and the columns are like:

ID  | field1 | field 2
-----------------
id  | null   | 3
id  | 1      | null
id  | null   | null
id  | 2      | 4

and the output can be like (id, 1,4) or (id,2,4) or ... but not something like (id, 1, null)

I can't find in the docs if any_value() is guaranteed to return a not null row if there is one (although it did so in my experiments) or may return a row with null value even if there are some not null values.

Does any_value() perform the task I described? If not what way to you suggest for doing it?

Answer 1

You can use analyatical functions as well.

Below is the query (SQL server):

select id, field1, field2 
  from (select id, field1, field2, row_number() 
    over (partition by id order by isnull(field1, 'ZZZ') asc, isnull(field2, 'ZZZ') asc) as RNK from mytable) aa 
  where aa.RNK = 1;

This will return only one row, you can change the order in order by clause if you are looking for maximun value in any column.

Answer 2

This could be achieved by aggregating to array with 'ignore nulls' specified and taking the first element of the resulting array. Unlike MIN/MAX solution, you can use it with structs

SELECT
  id,
  ARRAY_AGG(field1 IGNORE NULLS LIMIT 1)[SAFE_OFFSET(0)],
FROM
  mytable
GROUP BY
  id