How do I find the row with the latest date before another column?

Question

I have a query in Oracle that I've been banging my head against for a while now. It is composed of separate subqueries joined to each other, and the GROUP BY is messing things up.

SELECT TA.PROJECTID,
       TA.TASKID,
       TA.DUEDATE,
       MH.DATE_EXECUTION,
       MAX (TA.LAST_UPDATED_DATE) AS LAST_UPDATED_DATE
  FROM (TASKS TA
        INNER JOIN
        (SELECT PA.PROJECTID, MIN(PA.DATE_EXECUTION)
           FROM APPROVALS pa
         GROUP BY pa.PROJECTID) MH
           ON TA.PROJECTID = MH.PROJECTID)
 WHERE     
        TA.LAST_UPDATED_DATE <= MH.DATE_EXECUTION
 GROUP BY TA.PROJECTID,
          TA.TASKID,
          MH.DATE_EXECUTION,
          TA.DUEDATE

The first query block on the right side of the first inner join (select pa.projectid...) returns a table with project IDs and a DATE_EXECUTION column showing the date that the project was initiated - one line per project.

I am trying to return a single row per project ID with the latest LAST_UPDATED_DATE before the DATE_EXECUTION for that project. The grouping by DUEDATE is messing things up and causing it to return multiple rows. The idea here is that each row in the TASKS table is a different time a user updated the due date for the project, and I'm trying to identify the due date that was current when the project started execution (the DATE_EXECUTION).

I feel like I'm almost there, just not sure what I'm missing in my approach.

Sample data is:

PROJECTID	TASKID	DUEDATE	DATE_EXECUTION	LAST_UPDATED_DATE
abc123	1	2022-01-07	2021-03-31	2021-03-03
abc123	1	2021-12-20	2021-03-31	2021-03-11
abc123	1	2021-12-27	2021-03-31	2020-11-18
abc123	1	2022-01-01	2021-03-31	2021-05-01

So in the above data for the combination of project abc123 and task ID 1, I'd want the second row: the row where the LAST_UPDATED_DATE is the latest prior to the DATE_EXECUTION.

please edit the question and show the table DDL, example data, and expected output. thanks. — OldProgrammer, Jul 01 '21 at 18:41

score 1 · Answer 1 · answered Jul 01 '21 at 20:03

Thanks all, I think I solved it on my own using a WITH and selecting the MAX(LAST_UPDATED_DATE) OVER (PARTITION BY PROJECTID, TASKID) AS LATEST_UPDATE

and then selecting from that temp table defined by the WITH only the row where LATEST_UPDATE = LAST_UPDATED_DATE

WITH temptable 
AS (SELECT TA.PROJECTID,
       TA.TASKID,
       TA.DUEDATE,
       MH.DATE_EXECUTION,
       TA.LAST_UPDATED_DATE,
       MAX (TA.LAST_UPDATED_DATE) OVER (PARTITION BY TA.PROJECTID, TA.TASKID) AS LATEST_UPDATED
  FROM (TASKS TA
        INNER JOIN
        (SELECT PA.PROJECTID, MIN(PA.DATE_EXECUTION)
           FROM APPROVALS pa
         GROUP BY pa.PROJECTID) MH
           ON TA.PROJECTID = MH.PROJECTID)
 WHERE     
        TA.LAST_UPDATED_DATE <= MH.DATE_EXECUTION)

SELECT PROJECTID, TASKID, DUEDATE, DATE_EXECUTION, LAST_UPDATED_DATE
FROM temptable
WHERE LAST_UPDATED_DATE = LATEST_UPDATED

score 0 · Answer 2 · answered Jul 01 '21 at 19:33

Since the due_date is different for all records, So you can either drop the due_date column from the select list, That will solve your problem or you can try below approach -

SELECT TA1.PROJECTID,
       TA1.TASKID,
       TA2.DATE_EXECUTION,
       TA1.DUEDATE,
       TA1.LAST_UPDATED_DATE
  FROM TASKS TA1
 INNER JOIN (SELECT TA.PROJECTID,
                    TA.TASKID,
                    MH.DATE_EXECUTION,
                    MAX(TA.LAST_UPDATED_DATE) AS LAST_UPDATED_DATE
               FROM TASKS TA
              INNER JOIN (SELECT PA.PROJECTID, MIN(PA.DATE_EXECUTION)
                            FROM APPROVALS pa
                           GROUP BY pa.PROJECTID) MH ON TA.PROJECTID = MH.PROJECTID)
              WHERE TA.LAST_UPDATED_DATE <= MH.DATE_EXECUTION
              GROUP BY TA.PROJECTID,
                       TA.TASKID,
                       MH.DATE_EXECUTION) TA2 ON TA1.PROJECTID = TA2.PROJECTID
                                             AND TA1.TASKID = TA2.TASKID
                                             AND TA1.DUEDATE = TA2.DUEDATE
                                             AND TA1.LAST_UPDATED_DATE = TA2.LAST_UPDATED_DATE;

score 0 · Answer 3 · answered Jul 01 '21 at 19:46

I would use a correlated subquery like this:

select  ta.projectid,
        ta.taskid,
        ta.duedate,
        mh.date_execution,
        ta.last_updated_date
from    tasks                                                 ta
   join ( select    projectid,
                    min(date_execution) as date_execution
          from      approvals
          group by  projectid
        )                                                     mh
     on ta.projectid = mh.projectid
where   ta.last_updated_date =   (  select    max(x.last_updated_date) as last_updated_date
                                    from      tasks   x 
                                    where     x.projectid = ta.projectid 
                                          and x.last_updated_date <= mh.date_execution      );

score 0 · Answer 4 · answered Jul 01 '21 at 20:12

Remove DUEDATE from the GROUP BY and then aggregate it using KEEP (DENSE_RANK LAST LAST_UPDATED_DATE) to first filter out any rows in the aggregation that are not the latest LAST_UPDATED_DATE

SELECT TA.PROJECTID,
       TA.TASKID,
       MAX(TA.DUEDATE) KEEP (DENSE_RANK LAST ORDER BY TA.LAST_UPDATED_DATE)
         AS DUEDATE,
       MH.DATE_EXECUTION,
       MAX(TA.LAST_UPDATED_DATE) AS LAST_UPDATED_DATE
FROM   TASKS TA
       INNER JOIN (
         SELECT PA.PROJECTID,
                MIN(PA.DATE_EXECUTION)
         FROM   APPROVALS pa
         GROUP BY pa.PROJECTID
       ) MH
       ON TA.PROJECTID = MH.PROJECTID)
WHERE  TA.LAST_UPDATED_DATE <= MH.DATE_EXECUTION
GROUP BY
       TA.PROJECTID,
       TA.TASKID,
       MH.DATE_EXECUTION

How do I find the row with the latest date before another column?

4 Answers4