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?

I cannot follow your question. You have `id` in the query, but not in the sample data. You have three columns in your output, but it is confusing. — Gordon Linoff, Sep 27 '16 at 10:42

score 1 · Accepted Answer · answered Sep 27 '16 at 10:43

1

This is sort of a guess, but have you tried:

SELECT id, MIN(field1), MAX(field2)
FROM mytable
GROUP BY id;

This will ignore NULL values return different values from the two columns.

answered Sep 27 '16 at 10:43

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · edited Sep 27 '16 at 11:25

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.

score 0 · Answer 3 · answered Feb 02 '22 at 04:01

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

Get any not null value of other fileds in aggregations

3 Answers3