BigQuery SQL - how to pivot the top 3 results into columns

Question

Long time lurker first time poster...

I have a flat table like below and I want to bring back the top 3 finishers by event into separate columns.

with races as ( 
select "200M" as race, "johnson" as name,23.5 as finishtime
union all
select "200M" as race, "smith" as name,24.1 as finishtime
union all
select "200M" as race, "anderson" as name,23.9 as finishtime
union all
select "200M" as race, "jackson" as name,24.9 as finishtime
union all
select "400M" as race, "johnson" as name,47.1 as finishtime
union all
select "400M" as race, "alexander" as name,46.9 as finishtime
union all
select "400M" as race, "wise" as name,47.2 as finishtime
union all
select "400M" as race, "thompson" as name,46.8 as finishtime
    )

    select * from races

I would like the output to basically look like this:

Race  |  1st Place  |  2nd Place  | 3rd Place    
200M  |  johnson    |  anderson   | smith    
400M  |  thompson   |  alexander  | johnson

I didn't put a tie into the data above but I will have some of those too...

Thanks in advance!

Answer 1

Consider below

select * from (
  select race, name, 
    row_number() over(partition by race order by finishtime) pos
  from races
)
pivot (any_value(name) as place for pos in (1,2,3))

if applied to sample data in your question - output is