Как получить максимальную дату для нескольких условий

Немного новый для Oracle синтаксис SQL, более знакомый с MS SQL Server.

У меня проблемы с присоединением.

В частности, предполагается, что объединение позволит мне вернуть самую последнюю дату из таблицы. (В SQL Server я бы просто выбрал первый порядок дат по дате --- с оракулом это сбивает с толку!)

Вот весь мой запрос, я пытаюсь вернуть максимальную дату для каждого из столбцов даты в операторе выбора с префиксом MAX(. (Я знаю, что префикс max не является правильным синтаксисом, чтобы получить то, что мне нужно , я просто перечисляю это здесь, чтобы вы знали, на какие даты мне нужна максимальная дата..) Как мне получить самую последнюю дату для этих столбцов?:

SELECT DISTINCT
req.LCR_REQUEST_ID "Request_ID"
,req.MATTER_ID AS "MatterID_lcr"
,req.CUSTOMER_NAME AS "AccountName_lcr"
,req.STATUS AS "CurrentContractStatus_lcr"
,MAX(HIS.RECORDED_DATE) AS "CurrentStatus_ChangeDate"
--,HIS.STATUS AS "ContractStatus_Historic_lcr"
,MAX(HIS_SUB.RECORDED_DATE) AS "Submitted_Status_Date"
--,HIS_NA.RECORDED_DATE AS "NotAssigned_Status_Date"
,MAX(HIS_AtA.RECORDED_DATE) AS "AssignedToAttorney_Status_Date"
,MAX(HIS_SCIN.RECORDED_DATE) AS "InNegotiation_Status_Date"
,MAX(HIS_EXE.RECORDED_DATE) AS "Executed_Status_Date"
,MAX(HIS_CONV.RECORDED_DATE) AS "ConverteToAmend_Status_Date"
,MAX(HIS_DEAD.RECORDED_DATE) AS "Dead_Status_Date"
,MAX(HIS_COMP.RECORDED_DATE) AS "Completed_Status_Date"
,MAX(HIS_EXP.RECORDED_DATE) AS "Expired_Status_Date"
,CASE
WHEN req.DEALSIZE = 0 THEN ''
WHEN req.DEALSIZE = 1 THEN 'Less Than 100K'
when req.DEALSIZE = 2 THEN '100K-500K'
when req.DEALSIZE = 3 THEN '500K-1M'
when req.DEALSIZE = 4 THEN '1M-10M'
when req.DEALSIZE = 5 THEN '>10M'
END
AS "DealSize_lcr"
,cm_out.PTY_NAME AS "AccountName_cm"
,cm_out.PTY_ID AS "PTYID_CM"
,req.customer_id "PtyID_lcr"
,cm_out.EFFECTIVE_DATE "EffDate_lcr"
,cm_out.INITIALTERM_END_DATE "CPEDate_lcr"
,cm_out.CONTRACT_TERMINATION_DATE "FCTDate_lcr"
,cm_out.TERM_NOTICE_NONCOMP_REASON "ReasonForTermination_lcr"
,cm_out.TERM_TYPE "TermType_lcr"
,cm_out.INITIALTERM_DURATION "InitialTerm_lcr"
,cm_out.INVOICE_TIMEMEAS "InitialTermTymeMeas_lcr"
,cm_out.NUMBER_OF_RENEWALS "NumberOfRenewals_lcr"
,req.AMENDMENT "ParentMatterID_lcr"
,prod.rpg "RPG_lcr"
,prod.SALES_PRODUCT_NAME "Product_lcr"
,prod.CONTRACT_TYPE "ContractType_lcr"
,req.CONTRACT_ROLE "ContractRole_lcr"
,cm_out.METADATA_FLAG "MetaFlag_lcr"
,CASE 
WHEN cm_out.DATA_AUTO_IMPORT_YN = 1 THEN 'YES'
WHEN cm_out.REQUESTOR = 'Imported Record' THEN 'YES'
ELSE 'NO'
END
"DataImport_lcr"
,CASE act.actionstatus
WHEN  'Cancel Date Update' THEN 'Yes'
ELSE NULL
END "ActionToCancelAutoDate_lcr"
,req_emp.FIRST_NAME || ' ' || req_emp.LAST_NAME "Requestor_lcr"
,att_emp.FIRST_NAME || ' ' || att_emp.LAST_NAME  "Attorney_lcr"
,req.REQUESTOR_DEPARTMENT "Requestor_Department_lcr"
,req.AGREEMENT_TITLE "AgreementTitle_lcr"
,req.DATE_RECORDED
FROM LCR_REQUEST REQ
LEFT JOIN  LCR_ACTION ACT ON req.MATTER_ID = act.MATTER_ID
LEFT JOIN CM_CONTRACT_OUTBOUND cm_out ON cm_out.MATTER_ID = NVL(req.MATTER_ID,req.AMENDMENT)
LEFT JOIN LCR_REQUEST_PRODUCT prod_REQ ON prod_req.LCR_REQUEST_ID = req.LCR_REQUEST_ID
LEFT JOIN LCR_PRODUCT PROD ON prod.PRODUCT_ID = prod_req.PRODUCT_ID
LEFT JOIN LCR_EMPLOYEE att_emp ON req.ASSIGNED_TO = att_emp.ORACLE_PERSON_ID
LEFT JOIN LCR_EMPLOYEE req_emp ON req.REQUESTED_BY_ID = req_emp .ORACLE_PERSON_ID
LEFT JOIN LCR_STATUS_HISTORY HIS ON HIS.LCR_REQUEST_ID = req.LCR_REQUEST_ID AND ((HIS.STATUS = REQ.STATUS) OR (HIS.STATUS = 'Assigned to Attorney' AND REQ.STATUS = 'Assigned To Attorney'))
--LEFT JOIN LCR_STATUS_HISTORY HIS_NA ON req.LCR_REQUEST_ID = HIS_NA.LCR_REQUEST_ID AND HIS_NA.STATUS = 'Not Assigned'
LEFT JOIN LCR_STATUS_HISTORY HIS_AtA ON req.LCR_REQUEST_ID = HIS_AtA.LCR_REQUEST_ID AND HIS_AtA.STATUS = 'Assigned to Attorney'
LEFT JOIN LCR_STATUS_HISTORY HIS_SUB ON req.LCR_REQUEST_ID = HIS_SUB.LCR_REQUEST_ID AND HIS_SUB.STATUS = 'Submitted'
LEFT JOIN LCR_STATUS_HISTORY HIS_SCIN ON req.LCR_REQUEST_ID = HIS_SCIN.LCR_REQUEST_ID AND HIS_SCIN.STATUS = 'Submission Complete-In Negotiation'
LEFT JOIN LCR_STATUS_HISTORY HIS_EXE ON req.LCR_REQUEST_ID = HIS_EXE.LCR_REQUEST_ID AND HIS_EXE.STATUS = 'Executed'
LEFT JOIN LCR_STATUS_HISTORY HIS_CONV ON req.LCR_REQUEST_ID = HIS_CONV.LCR_REQUEST_ID AND HIS_CONV.STATUS = 'Converted to amendment.'
LEFT JOIN LCR_STATUS_HISTORY HIS_DEAD ON req.LCR_REQUEST_ID = HIS_DEAD.LCR_REQUEST_ID AND HIS_DEAD.STATUS = 'Dead'
LEFT JOIN LCR_STATUS_HISTORY HIS_COMP ON req.LCR_REQUEST_ID = HIS_COMP.LCR_REQUEST_ID AND HIS_COMP.STATUS = 'Completed'
LEFT JOIN LCR_STATUS_HISTORY HIS_EXP ON req.LCR_REQUEST_ID = HIS_EXP.LCR_REQUEST_ID AND HIS_EXP.STATUS = 'Expired/Terminated'
WHERE REQ.CATEGORY_NAME = 'Sales'
AND
REQ.DATE_RECORDED BETWEEN to_date ('07/01/2012', 'mm/dd/yyyy') AND to_date  ('06/30/2013','mm/dd/yyyy')
GROUP BY
req.LCR_REQUEST_ID
,req.MATTER_ID
,req.CUSTOMER_NAME
,req.STATUS
,HIS.RECORDED_DATE
--,HIS.STATUS
,HIS_SUB.RECORDED_DATE
--,HIS_NA.RECORDED_DATE
,HIS_AtA.RECORDED_DATE
,HIS_SCIN.RECORDED_DATE
,HIS_EXE.RECORDED_DATE
,HIS_CONV.RECORDED_DATE
,HIS_DEAD.RECORDED_DATE
,HIS_COMP.RECORDED_DATE
,HIS_EXP.RECORDED_DATE
,CASE
WHEN req.DEALSIZE = 0 THEN ''
WHEN req.DEALSIZE = 1 THEN 'Less Than 100K'
when req.DEALSIZE = 2 THEN '100K-500K'
when req.DEALSIZE = 3 THEN '500K-1M'
when req.DEALSIZE = 4 THEN '1M-10M'
when req.DEALSIZE = 5 THEN '>10M'
END
,cm_out.PTY_NAME
,cm_out.PTY_ID
,req.customer_id
,cm_out.EFFECTIVE_DATE
,cm_out.INITIALTERM_END_DATE
,cm_out.CONTRACT_TERMINATION_DATE
,cm_out.TERM_NOTICE_NONCOMP_REASON
,cm_out.TERM_TYPE
,cm_out.INITIALTERM_DURATION
,cm_out.INVOICE_TIMEMEAS
,cm_out.NUMBER_OF_RENEWALS
,req.AMENDMENT
,prod.rpg
,prod.SALES_PRODUCT_NAME
,prod.CONTRACT_TYPE
,req.CONTRACT_ROLE
,cm_out.METADATA_FLAG
,CASE 
WHEN cm_out.DATA_AUTO_IMPORT_YN = 1 THEN 'YES'
WHEN cm_out.REQUESTOR = 'Imported Record' THEN 'YES'
ELSE 'NO'
END
,CASE act.actionstatus
WHEN  'Cancel Date Update' THEN 'Yes'
ELSE NULL
END
,req_emp.FIRST_NAME || ' ' || req_emp.LAST_NAME
,att_emp.FIRST_NAME || ' ' || att_emp.LAST_NAME
,req.REQUESTOR_DEPARTMENT
,req.AGREEMENT_TITLE
,req.DATE_RECORDED;