How do you return a specific row per group by some criteria in BigQuery?-CodePudding

I have a table of people with firstname, surname, and age. I would like to retrieve the oldest person in each family (by surname). I don't want to just return the surname and age (via MAX(age) and GROUP BY surname) I want the entire row.

For example if my data is:

firstname, surname, age
john, smith, 31
sally, smith, 33
bob, smith, 34
john wayne, 35
bob wayne, 31

I would like my query to return:

firstname, surname, age
bob, smith, 34
john wayne, 35

CodePudding user response：

Consider below approach

select surname, array_agg(struct(firstname, age) order by age desc limit 1)[offset(0)].*
from your_table
group by surname

if applied to sample data in your question - output is