Oracle SQL getting max date value from when joining to another table

Question

I have a table structure where i can have multiple dates logged in a one to many relationship and need to retrieve the MAX date value in the table. Can anyone advise what the best recommended method would be to perform a join to obtain the MAX date value from a table? Specifically in the code below I would want to tweak this query to retrieve the max date value for the t.TimeEst field.

    SELECT
    a.VolumeID "WFID",
    a.Manager,
    t.TimeEst,
    t.SALTRecordedBy,
    t.SALTRecordedDate

   FROM 
   ctxAssistanceWF a

   JOIN ctxAssistanceSaltWF t
   ON a.VolumeID=t.SaltWFID

   WHERE
   a.RowSeqNum=1
   AND TO_CHAR(t.SALTRecordedDate, 'MM/DD/YYYY') = TO_CHAR(SYSDATE, 'MM/DD/YYYY')

WW. WW. · Accepted Answer · 2014-06-12T03:10:22

Use a GROUP BY so that you can use the aggregate MAX function:

SELECT a.VolumeID "WFID"
     , a.Manager
     , t.TimeEst
     , t.SALTRecordedBy
     , MAX(t.SALTRecordedDate)
FROM ctxAssistanceWF a JOIN ctxAssistanceSaltWF t ON a.VolumeID = t.SaltWFID  
WHERE a.RowSeqNum = 1  
GROUP BY a.VolumeID
       , a.Manager
       , t.TimeEst
       , t.SALTRecordedBy

Oracle SQL getting max date value from when joining to another table

1 Answers