SQL Server Said, PostgreSQL Said. APPLY and LATERAL

SQL Server CROSS APPLY:

select m.*, elder.*
from Member m
cross apply
(
    select top 1 ElderBirthDate = x.BirthDate
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
) as elder
order by m.BirthDate

Equivalent on PostgreSQL:

select m.*, elder.*
from Member m
join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
 limit 1
) as elder on true -- Though PostgreSQL's LATERAL offers more flexibility than SQL Server's APPLY, I haven't yet found a use case to use a condition on LATERAL, hence the hardcoded true
order by m.BirthDate

Note, a JOIN LATERAL(explicitly INNER JOIN LATERAL) with a condition of always true, is essentially a cross join. We can rewrite the PostgreSQL code above as follows:

select m.*, elder.*
from Member m
cross join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
    limit 1
) as elder -- we don't need to put any condition here anymore since this is a cross join
order by m.BirthDate

SQL Server OUTER APPLY:

select m.*, elder.*
from Member m
outer apply
(
    select top 1 ElderBirthDate = x.BirthDate, ElderFirstname = x.Firstname
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
) as elder
order by m.BirthDate

Equivalent on PostgreSQL:

select m.*, elder.*
from Member m
left join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
    limit 1
) as elder on true -- Though PostgreSQL's LATERAL offers more flexibility than SQL Server's APPLY, I haven't yet found a use case to use a condition on LATERAL, hence the hardcoded true
order by m.BirthDate

Related: http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html

Example: http://www.sqlfiddle.com/#!17/50ee1/8

Happy Coding! ツ