SQL如何对两个值分别排序

Question

请教各位
运行下列代码，得到排名前10的男性名字，把M换成F，得到排名前10的女性名字。有没有可能一次得出女性和男性名字分别的前10名并且先排一个性别再排另一个？
多谢！

SELECT name,gender,SUM(number) AS total
FROM bigquery-public-data.usa_names.usa_1910_current
WHERE gender ='M'
GROUP BY name,gender
ORDER BY total DESC
LIMIT 10

Answer 1

窗口函数

select * from (
select *, row_num() over(partition by gender order by name) rn from bigquery-public-data.usa_names.usa_1910_current
 ) t where t.rn <= 10