Como trazer apenas o último registro quando uso CASE WHEN na coluna CASE_ATTR_TYPE que trás várias informações do mesmo id.

Preciso trazer apenas o registro mais recente das colunas que tem CASE WHEN
Provavelmente é um sub select, mas ainda não to dominando muito bem.





SELECT
DISTINCT b.CASE_ID as ID_DO_CASO,
c.CASE_TYPE as TIPO,
c.CASE_STATUS as STATUS,
TIMESTAMP_DIFF(c.CASE_CREATION_DATE, r.SHP_DATETIME_DELIVERED_ID,HOUR) as ANULADO,
CASE WHEN ass.CASE_ASSIGN_USER_ID is not null and atr.CASE_ATTR_TYPE = ''''PACKAGE_DELIVERED'''' then ''''Revertido'''' else " " end as REVERTIDO,
c.CASE_CLOSE_COMMENT as DETALHE_DA_GESTAO,
c.FACILITY_ID as FACILITY,
shp.SHP_LG_CLUSTER_ID as NOME_DA_ROTA,
c.CASE_REF_ROUTE_ID as ID_ROTA,
comp.shp_company_name as TRANSPORTADORA,
CONCAT (d.SHP_LG_DRIVER_FIRST_NAME, '''' '''', SHP_LG_DRIVER_LAST_NAME) as MOTORISTA,
c.CASE_CREATION_DATE + INTERVAL ''''1'''' HOUR as DATA_DE_CRIACAO,
c.CASE_ASSIGNMENT_DATE + INTERVAL ''''1'''' HOUR as DATA_DE_ATRIBUICAO,
c.CASE_RESOLUTION_DATE + INTERVAL ''''1'''' HOUR as DATA_DE_RESOLUCAO,
ass.CASE_ASSIGN_USER_ID as REP_ID,
b.CASE_AUTOMATIC_CREATION as CRIACAO,
c.CASE_REF_PLACES_ID as ID_DA_AGENCIA,
CASE WHEN atr.CASE_ATTR_TYPE = ''''SAME_DAY'''' THEN ''''SD'''' ELSE '''' '''' END as SD,
CASE WHEN atr.CASE_ATTR_TYPE = ''''CONTACT_BUYER'''' THEN ''''Entrei em contato com o comprador'''' ELSE '''' '''' END as CONTATO,
CASE WHEN atr.CASE_ATTR_TYPE = ''''PACKAGE_DELIVERED'''' then ''''Pacote entregue'''' else ''''Pacote não entregue'''' end AS DETALHE_DO_FECHAMENTO,
--CASE WHEN atr.CASE_ATTR_TYPE = "" as MOTIVO_DE_NAO_PEDIR_NOVA_TENTATIVA,
r.SHP_DATETIME_DELIVERED_ID + INTERVAL ''''1'''' HOUR as DATA_DETALHE_DO_FECHAMENTO,
c.CASE_REF_SHIPMENT_ID as SHIP_ID,
CASE WHEN atr.CASE_ATTR_TYPE = ''''ADJUSTED_ADDRESS'''' then ''''Ajustou o endereço'''' else '''' '''' END AS AJUSTE_DE_ENDERECO,
CAST (shp.SHP_LG_PROMISE_DT_FROM as date) + INTERVAL ''''1'''' day as PROMESSA_DE_ENTREGA

FROM
WHOWNER.BT_SHP_TOC_CASES c
LEFT JOIN
WHOWNER.BT_LG_TOC_CASE_BASE b
ON c.CASE_ID = b.CASE_ID
LEFT JOIN
WHOWNER.BT_SHP_TOC_CASES_ATTRIBUTES atr
ON atr.CASE_ID = c.CASE_ID
LEFT JOIN
WHOWNER.LK_SHP_COMPANIES comp
ON c.CASE_REF_CARRIER_ID = comp.SHP_company_ID
LEFT JOIN
WHOWNER.BT_SHP_TOC_CASES_ASSIGNMENTS ass
ON ass.CASE_ID = c.CASE_ID
LEFT JOIN
WHOWNER.LK_SHP_LG_DRIVERS d
ON
d.SHP_LG_DRIVER_ID = c.CASE_REF_DRIVER_ID
LEFT JOIN
WHOWNER.BT_SHP_LG_SHIPMENTS shp
ON
c.CASE_REF_SHIPMENT_ID = shp.SHP_SHIPMENT_ID
LEFT JOIN
WHOWNER.BT_SHP_SHIPMENTS r
ON
r.SHP_SHIPMENT_ID = c.CASE_REF_SHIPMENT_ID


WHERE c.SIT_SITE_ID = ''''MLB''''
AND
c.CASE_APPLICATION = ''''LOGISTICS_TOC_LASTMILE''''
--AND c.case_type IN (''''BUSINESS_CLOSED'''', ''''BUYER_ABSENT'''', ''''BAD_ADDRESS'''')
--and SHIPPING_BI.FN_API_TIMEZONE(c.SIT_SITE_ID,c.case_creation_date) >= current_date -3

AND CAST(c.case_creation_date as DATE) BETWEEN ''''2022-02-28'''' and ''''2022-02-28''''