sql - How can i get top row in partition?

Question

Sport	Athlete	Year	Medal
hockey	COWLEY, Gillian Margaret	1980	Gold
hockey	DAVIES, Patricia Joan	1980	Gold
rowing	REIC, Josip	1980	Bronze
Aquatics	COVENTRY, Kirsty	2004	Silver
Boxing	SKARO, Damir	1988	Bronze
Boxing	KACAR, Slobodan	1980	Gold

question from:https://stackoverflow.com/questions/65946344/how-can-i-get-top-row-in-partition

Answer 1

One method is a subquery:

SELECT t.*
FROM (SELECT ROW_NUMBER() over (PARTITION BY Sport ORDER BY Athlete ) as rn, Athlete, Event 
      FROM `table`
     ) t
WHERE rn = 1;

A more colloquial way to do this in BQ uses aggregation, though:

select array_agg(t order by athlete asc limit 1)[ordinal(1)].*
from `table` t
group by sport;