CODEWITHSUNDEEP
google-bigquery
Maksym Katsovets
Maksym Katsovets

Reputation: 147

How to GROUP ARRAY in right way

I need to receive participant_name of winner per each race_name from the following table 

WITH race AS (    
  SELECT 'A' AS race_name, [4, 1, 3] as participant_id, [25.3, 29.4, 24.1] as timing  UNION ALL    
  SELECT 'B', [2, 1, 4, 6], [16.3, 14.2, 17.4, 18.3] UNION ALL    
  SELECT 'C', [7, 5, 4, 2], [19.5, 21.4, 16.4, 15.6]     
),

participant AS (    
  SELECT 1 as participant_id, 12 as participant_age, "Julie" as participant_name UNION ALL    
  SELECT 2, 11, "Max" UNION ALL    
  SELECT 3, 12, "John" UNION ALL    
  SELECT 4, 11, "Matt" UNION ALL    
  SELECT 5, 13, "Diana" UNION ALL    
  SELECT 6, 12, "Peter" UNION ALL    
  SELECT 7, 11, "Ben"     
)

SELECT race_name, 
  (SELECT participant_name 
    FROM participant CROSS JOIN UNNEST(timing) AS duration 
    ORDER BY duration ASC 
    LIMIT 1
  )
FROM race

I receive participant_name of the participant with the lowest timing, but not per each race_name. How to fix it?

Upvotes: 1

Views: 65

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
SELECT race_name, 
  (SELECT participant_name
    FROM UNNEST(participant_id) AS participant_id WITH OFFSET 
    JOIN UNNEST(timing) AS duration WITH OFFSET USING(OFFSET)
    LEFT JOIN participant USING(participant_id)
    ORDER BY duration ASC 
    LIMIT 1
  ) participant_name 
FROM race

If to apply to sample data from your question - result is 

Row race_name   participant_name     
1   A           John     
2   B           Julie    
3   C           Max

Upvotes: 2

Related Questions