Search code examples
aggregaterow-numberrownum

Difficulty getting the desired result in Oracle


I have 2 tables in my Oracle DB with the following structure and data:

Table 1: Employees

Emp_ID Name
E1 A
E2 B
E3 C

Table 2: Dependants (employees children with name and birth dates)

Emp_ID Depdt_ID Depdt_name Depdt_dob
E1 D1 Aa 2020-12-31
E1 D2 Ab 2012-07-14
E2 D3 Ba 2017-01-30
E2 D4 Bb 2001-03-21
E2 D5 Bc 2005-12-06
E2 D6 Bd 2012-01-14
E3 D7 Ca 1999-05-20
E1 D8 Ac 2021-07-28

I need the list of workers and for each of them the name and birth date of their youngest child: Result needed:

Emp_ID Depdt_name Depdt_dob
E2 Ba 2017-01-30
E3 Ca 1999-05-20
E1 Ac 2021-07-28

How can I achieve this result in Oracle?

Rownum in a subquery, but I still don't understand the result.


Solution

  • SELECT
       Emp_id,
       Depdt_name,
       Depdt_dob,
       RANK() OVER(PARTITION BY Emp_ID ORDER BY Depdt_dob DESC) AS dpdt_age_rank
    FROM Employees AS e
    LEFT JOIN Dependant AS d
    ON e.Emp_ID = d.Emp_ID
    WHERE dpdt_age_rank = 1
    

    This will rank all dependants, ordered by date of birth from youngest to oldest and partitioned per employee id and filter only for the youngest, even if there are any twins. Note that if you use ROW_NUMBER() instead of RANK() you would lose any twins cause it does not tie results.