用于检索组中某个点的最高项的SQL查询

select p.*
from person p
inner join (
    select surname, max(age) as maxage
    from person 
    where age < 30
    group by surname
) pm on p.surname = pm.surname and p.age = pm.maxage

此版本只需要传递一次数据:

SELECT DISTINCT
       FIRST_VALUE(name)
       OVER (PARTITION BY surname ORDER BY age DESC) name
      ,FIRST_VALUE(surname)
       OVER (PARTITION BY surname ORDER BY age DESC) surname
      ,FIRST_VALUE(age)
       OVER (PARTITION BY surname ORDER BY age DESC) age
FROM   person
WHERE  age < 30;

警告:如果打领带(即同一家庭中两个年龄相同的人),只会选一条。