How to filter value in Big query

Question

I have Table1 :

ID Name Tag Date

1. abc good 2/01/2022
2. bcd good 2/01/2022
3. def bad 2/02/2022
4. abc bad 2/02/2022
5. bcd bad 2/02/2022
6. abc good 2/03/2022
7. abc bad 2/04/2022

Result :

id Name Tag Date

1. abc good 2/01/2022
2. bcd good 2/01/2022
3. def bad 2/02/2022

I am trying to get the Tag detail for particular name based on min date. How to filter out the data because when i am doing big query i have to add Tag in group it goes back square one. can you help me

Answer 1

Consider below approach

select as value array_agg(t order by date limit 1)[offset(0)]
from  your_table t
group by name        

if applied to sample data in your question - output is