CODEWITHSUNDEEP
sqloracle-database
Jonathan Morningstar
Jonathan Morningstar

Reputation: 431

Most recent child record keyed

How do I get the date of the most recent child record? In the SQL, I am trying to get the latest MOPNOTES.MOPNOTEDATE. I am also wanting only one record per MOPACTIVITY.MOPID. Any direction would be greatly appreciated.

SELECT DISTINCT MOPACTIVITY.MOPID,
MOPNOTES.MOPNOTEDATE 
FROM MOPUSER.MOPACTIVITY INNER JOIN MOPUSER.MOPNOTES ON MOPACTIVITY.MOPID=MOPNOTES.MOPID
WHERE MOPACTIVITY.MOPEND BETWEEN TRUNC(SYSDATE-1) + INTERVAL '12:00' HOUR TO MINUTE AND TRUNC(SYSDATE) + INTERVAL '9:00' HOUR TO MINUTE
AND UPPER(MOPACTIVITY.MOPSTATUS) = 'COMPLETE'
AND UPPER(MOPACTIVITY.MOPNOTIFICATIONSENT) LIKE '%TRUE%'
ORDER BY MOPACTIVITY.MOPID

Upvotes: 0

Views: 37

Answers (1)

xQbert
xQbert

Reputation: 35343

You're looking for the MAX aggregate function on a date field in combination with a group by on the MOPID to Group all like Ids and return the most recent date. Distinct wasn't working for you becuase you had different dates. By only returning the max you get 1 date and 1 ID is generated using the group by.

SELECT MOPACTIVITY.MOPID, Max(MOPNOTES.MOPNOTEDATE)
FROM MOPUSER.MOPACTIVITY 
INNER JOIN MOPUSER.MOPNOTES 
  ON MOPACTIVITY.MOPID=MOPNOTES.MOPID
WHERE MOPACTIVITY.MOPEND BETWEEN TRUNC(SYSDATE-1) + INTERVAL '12:00' HOUR TO MINUTE AND TRUNC(SYSDATE) + INTERVAL '9:00' HOUR TO MINUTE
AND UPPER(MOPACTIVITY.MOPSTATUS) = 'COMPLETE'
AND UPPER(MOPACTIVITY.MOPNOTIFICATIONSENT) LIKE '%TRUE%'
GROUP BY MOPACTIVITY.MOPID
ORDER BY MOPACTIVITY.MOPID

Upvotes: 1

Related Questions