Selecting the min num for each value of a column

Question

I have a table that is like this:

col1       col2       num
a        <string1>     5
a        <string2>     10
a        <string3>     0
a        <string4>     7
b        <string1>     6
b        <string2>     3
b        <string3>     20
b        <string4>     1

I want to select the min num for each value of col1 (or eventually col2), so the desired output would be:

col1       col2        num
a        <string3>     0
b        <string4>     1

How can I achieve this? I'm trying to do this in BigQuery.

Answer 1

If you are running Postgres (as tagged), you can use distinct on:

select distinct on (col1) t.*
from mytable t
order by col1, num

In BigQuery (as mentionned in the question), you can do this with arrays:

select array_agg(t order by num limit 1)[offset(0)].*
from mytable t
group by col1