Retrieve max date

I am trying to retrieve only the record with the max ELIGIBLE date. I have two orders in a table with the same order number. The difference in the orders is that the ELIGIBLE dates are different. I only want to retrieve the record with the latest ELIGIBLE date.

The order information in ord_order table.

ORDER NUMBER    | APPOINTMENT TYPE | ELIGIBLE
----------------+------------------+----------
264000000000382 | 109000000000001  | 11-JAN-16  
264000000000382 | 109000000000005  | 06-NOV-19

I expect to only pull the information with the ELIGIBLE date as 06-NOV-19.

select LOGON_ID, LAB_USER.NAME, C.NAME as BU, F.NAME as AREA,  ORD.position_lat, ord.position_long,  
CAST((FROM_TZ(CAST(D.OCCURRED_AT AS TIMESTAMP),'+00:00') AT TIME ZONE 'EST5EDT' ) AS DATE) OCCURRED, CND_EVENT_DEF.CORE_DESCRIPTION, E.PARAM_VALUE
,(CASE WHEN CND_EVENT_DEF.CORE_DESCRIPTION = 'AsnAssignmentOnSite_evt' THEN CAST((FROM_TZ(CAST(D.OCCURRED_AT AS TIMESTAMP),'+00:00') AT TIME ZONE 'EST5EDT' ) AS DATE) END ) Arrival
,(CASE WHEN CND_EVENT_DEF.CORE_DESCRIPTION = 'OrdActivityComplete_evt' THEN CAST((FROM_TZ(CAST(D.OCCURRED_AT AS TIMESTAMP),'+00:00') AT TIME ZONE 'EST5EDT' ) AS DATE) END) Completion
,(CASE WHEN CND_EVENT_DEF.CORE_DESCRIPTION = 'AsnAssignmentEnRoute_evt' THEN CAST((FROM_TZ(CAST(D.OCCURRED_AT AS TIMESTAMP),'+00:00') AT TIME ZONE 'EST5EDT' ) AS DATE) END) Enroute
from 
  (select * 
  from ELOG_EVENT 
    where trunc(FROM_TZ(CAST(OCCURRED_AT AS TIMESTAMP),'+00:00') at TIME ZONE 'EST5EDT') between trunc(FROM_TZ(CAST(sysdate AS TIMESTAMP),'+00:00') at TIME ZONE 'EST5EDT')-365 and trunc(FROM_TZ(CAST(sysdate AS TIMESTAMP),'+00:00') at TIME ZONE 'EST5EDT')-365+366
      and ODB_DELETED is NULL 
      and EVENT_TYPE != 526000000000157) D
  inner join (select FOR_USER, BUSINESS from LAB_USER_BUSINESS where ORDINAL = 0 and ODB_DELETED is NULL) B on (B.FOR_USER = D.FOR_USER)
  left join (select BUSINESS_ID, NAME from LAB_BUSINESS where NAME ='EFO') C on (C.BUSINESS_ID = B.BUSINESS)
  inner join (select EVENT, PARAM_VALUE, ENTITY_KEY from ELOG_PARAM) E on (E.EVENT = D.EVENT_ID)
  left join LAB_USER on (LAB_USER.USER_ID = D.FOR_USER)
  inner join (select position_lat, position_long, max(eligible), order_num, dispatch_area from ord_order group by position_lat, position_long, order_num, dispatch_area) ORD on (E.param_value=ord.order_num)
  inner join AREA_NODE F on (F.NODE_ID = ORD.DISPATCH_AREA)
inner join CND_EVENT_DEF on (CND_EVENT_DEF.EVENT_DEF_ID = D.EVENT_TYPE)
where 
  (E.ENTITY_KEY is NULL and E.PARAM_VALUE != '0') 
    or (E.ENTITY_KEY = ORD.DISPATCH_AREA and D.EVENT_TYPE not in (526000000000228,526000000000229))
    AND C.NAME IS NOT NULL
    group by LOGON_ID, LAB_USER.NAME, C.NAME, f.name, CND_EVENT_DEF.CORE_DESCRIPTION, E.PARAM_VALUE, occurred_at, ord.position_lat, ord.position_long

I expect to only pull the information with the ELIGIBLE date as 06-NOV-19.

Answer

According to your description you simply want to retrieve the row with the maximum eligible date. That is:

select * from ord_order where eligible = (select max(eligible) from ord_order);

In your query:

inner join
(
  select * from ord_order where eligible = (select max(eligible) from ord_order)
) ord on e.param_value = ord.order_num

With multiple orders:

inner join
(
  select * from ord_order where (order_num, eligible) = 
    (select order_num, max(eligible) from ord_order group by order_num)
) ord on e.param_value = ord.order_num

Advertisement

Answer