select
    a.emp_no,
    a.birth_date,
    a.first_name,
    a.last_name,
    a.gender,
    a.hire_date
from
    (
        select
            *,
            dense_rank() over (
                order by
                    hire_date desc
            ) as ranking
        from
            employees
    ) a
where
    ranking = 3
order by
    a.emp_no asc