CODEWITHSUNDEEP
sqlgoogle-bigquerygreatest-n-per-group
Pedro Pablo Severin Honorato
Pedro Pablo Severin Honorato

Reputation: 417

Selecting the min num for each value of a column

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.

Upvotes: 0

Views: 43

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL

#standardSQL
select as value array_agg(t order by num limit 1)[offset(0)]
from `project.dataset.table` t
group by col1

if to apply to sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  select 'a' col1, '<string1>' col2, 5 num union all
  select 'a', '<string2>', 10 union all
  select 'a', '<string3>', 0 union all
  select 'a', '<string4>', 7 union all
  select 'b', '<string1>', 6 union all
  select 'b', '<string2>', 3 union all
  select 'b', '<string3>', 20 union all
  select 'b', '<string4>', 1 
)
select as value array_agg(t order by num limit 1)[offset(0)]
from `project.dataset.table` t
group by col1

the output is

enter image description here

Upvotes: 1

GMB
GMB

Reputation: 222432

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

Upvotes: 0

Related Questions