Difficulty getting the desired result in Oracle

Question

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.

Answer 1

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.

Answer 2

I ended up finding a solution to my question thanks to @arthurq's answer above.

Here is the final form of the query:

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

Hope this helps!