Oracle – Selecting record based on latest date

In this query, in the ODF_CA_NL_INIT_REQ_NOTE n table, there can be multiple NL_NOTEs per each record in the ODF_CA_OTHER o table. One to many relationship.

I want to select the n.NL_NOTE for each record with the latest n.LAST_UPDATED_DATE.

I added the subselect as seen below, but for each record returned in the query, the NOTE field is just showing the latest n.NL_NOTE from the entire ODF_CA_NL_INIT_REQ_NOTE n table.

How do I show, for each record returned in the query, the latest n.NL_NOTE for each related record and not for the entire table?

             SELECT
                 i.CODE as INITIATIVE_ID,
                 i.NAME AS INITIATIVE,
                 CONCAT(CONCAT(CONCAT(u2.LAST_NAME, ', '), u2.FIRST_NAME), NVL(u3.MIDDLE_NAME, '')) AS DIRECTOR_NAME,
                 o.NL_DIRECT AS DIRECTOR,
                 CONCAT(CONCAT(CONCAT(u.LAST_NAME, ', '), u.FIRST_NAME), NVL(u3.MIDDLE_NAME, '')) AS MANAGER_NAME,
                 o.NL_INIT_MANAGER AS MANAGER,
                 u.EMAIL AS MANAGER_EMAIL,
                 CONCAT(CONCAT(CONCAT(u3.LAST_NAME, ', '), u3.FIRST_NAME), NVL(u3.MIDDLE_NAME, '')) AS TEAMLEAD_NAME,
                 o.NL_TEAM_LEAD AS TEAMLEAD,
                 NVL(o.NL_TEAM, '') AS TEAM,
                 NVL(i.DESCRIPTION, '') AS DESCRIPTION,

                 (SELECT n.NL_NOTES
                  FROM ODF_CA_NL_INIT_REQ_NOTE n
                  JOIN ODF_CA_OTHER o ON o.ID = n.ODF_PARENT_ID
                  WHERE n.LAST_UPDATED_DATE = (SELECT
                                               MAX(n.LAST_UPDATED_DATE)
                                               FROM ODF_CA_NL_INIT_REQ_NOTE n
                                               JOIN ODF_CA_OTHER o ON o.ID = n.ODF_PARENT_ID)) AS NOTE,

                 o.NL_PRIOR AS PRIORITY,
                 o.NL_STATUS AS INITIATIVE_STATUS,
                 TO_CHAR(TRUNC(o.LAST_UPDATED_DATE), 'Mon DD, YYYY') AS LUD,
                 TO_CHAR(TRUNC(i.SCHEDULE_START), 'Mon DD, YYYY') AS STARTDATE,
                 TO_CHAR(TRUNC(i.SCHEDULE_FINISH), 'Mon DD, YYYY') AS ENDDATE,
                 CASE
                     WHEN TRUNC(sysdate) BETWEEN TRUNC(i.SCHEDULE_FINISH - 10) AND TRUNC(i.SCHEDULE_FINISH) THEN 0
                     WHEN TRUNC(sysdate) = TRUNC(i.SCHEDULE_FINISH) THEN 1
                     WHEN TRUNC(sysdate) > TRUNC(i.SCHEDULE_FINISH) THEN 2
                     ELSE 3
                 END AS SCHEDULE_STATUS
             FROM
                 ODF_CA_OTHER o
             JOIN
                 INV_INVESTMENTS i ON i.ID = o.ID
             JOIN
                 SRM_RESOURCES u ON o.NL_MANAGER = u.ID
             JOIN
                 SRM_RESOURCES u2 ON o.NL_DIR = u2.ID
             JOIN
                 CMN_SEC_USERS u3 ON o.NL_LD_ANYST = u3.ID
             JOIN
                 ODF_CA_NL_INIT_REQ_NOTE n ON n.ODF_PARENT_ID = o.ID
             ORDER BY
                 o.NL_DIR ASC,
                 o.NL_MANAGER ASC,
                 o.NL_LD_ANYST ASC

Thanks

Answer

One way to do it is analytic function with first/last option:

^dbfiddle

select o.nl_direct as director, o.nl_prior as priority,
       (select max(nl_notes) keep (dense_rank last order by last_updated_date)
          from odf_ca_nl_init_req_note n where n.odf_parent_id = o.id) as note
  from odf_ca_other o