I would like to aggregate this table by id as shown below. The priorities are gold, silver, bronze, in that order, referring to the "type column".
priorities: gold > silver > bronze
| id | type | text |
|---|---|---|
| 1 | gold | banana |
| 1 | silver | apple |
| 2 | silver | orange |
| 2 | bronze | avocado |
| 3 | gold | kiwi |
| 3 | bronze | blueberry |
| id | text |
|---|---|
| 1 | banana |
| 2 | orange |
| 3 | kiwi |
Try array_agg:
select id, array_agg(text order by length(type) limit 1)[offset(0)] as text
from mytable
group by id
Consider below
select id,
array_agg(text order by strpos(',bronze,silver,gold,', ',' || type || ',') desc limit 1)[offset(0)] as text
from `project.dataset.table`
group by id
if applied to sample data in your question - output is
Another way is
select id, text
from `project.dataset.table`
where true
qualify row_number() over(partition by id order by strpos(',bronze,silver,gold,', ',' || type || ',') desc) = 1
with same output