SQL Query to retrieve highest item up to a point in a group

Question

The best way of describing this is I have a table of people with their names and ages. Assume that people with the same surname are from the same family. I need a query in oracle which will retrieve a list of the oldest person in each family, but not older than a certain age.

Table: person

name      surname         age
===============================
James     Smith           23
Sarah     Powell          17
Barry     Smith           31
Mark      Smith           35
Mary      Smith           18
Bob       Powell          30

How do I retrieve the oldest person in each family under 30?

Results I'm after

name      surname         age
===============================
James     Smith           23
Sarah     Powell          17

Answer 1

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

Answer 2

This version will only require one pass through the data:

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;

Warning: in the event of a tie (i.e. two people with identical ages in the same family), it will just pick one.