help needed with the query

curious_mind

Hi All,

I have a query,

Select distinct eods_grp_id,GRP_FP_IND,max(evnt_tstp)evnt_tstp from (

  (SELECT FG.eods_grp_id,EVNT_TYP_CDE,

  CASE 

  WHEN FG.eods_grp_id IS NOT NULL AND FB.EVNT_TYP_CDE IN ('planShared','firmPublishedImmediately') THEN 'Y'

  WHEN FG.eods_grp_id IS NOT NULL AND FB.EVNT_TYP_CDE IS NULL THEN 'N'

  WHEN FG.eods_grp_id IS NOT NULL AND FB.EVNT_TYP_CDE in ('accessRevoked','factFinder','dataGathering',  'factFinderCompleted','dataGatheringCompleted','factFinderCanceled','firmCreatedConsolidated') AND FB.EVNT_TSTP > TRUNC(SYSDATE) - interval '90' day THEN 'N'

  ELSE 

  'Y' END AS GRP_FP_IND,FB.evnt_tstp

  FROM fp_grp FG, fp_bus_evnt FB,grp g

  WHERE FG.fp_grp_id = FB.fp_grp_id

      and FG.eods_grp_id = g.eods_grp_id

      and g.GRP_CTG_CDE = '001'

     and g.GRP_INAC_DTE is null  

     and g.prtt_cde = 'CGA_1'

  AND FG.prtt_cde = 'FP_0'

  AND FB.prtt_cde = 'FP_0'

      and g.eods_grp_id='13193049'))

  GROUP BY eods_grp_id,GRP_FP_IND;

and data is coming like this:

EODS_GRP_ID GRP_FP_IND EVNT_TSTP

13193049 Y 20-JAN-21 07.37.41.000000000 PM

13193049 N 20-JAN-21 07.34.26.000000000 PM

but my requirement is to get this particular record because it has latest timestamp.

13193049 Y

please suggest on this.

RogerT

So you do not need a grouping ... as you are not combining rows, you just need the one with the highest timestamp...

select distinct eods_grp_id
        ,GRP_FP_IND
        ,evnt_tstp
 from (select FG.eods_grp_id
       , EVNT_TYP_CDE
       , case
         when   FG.eods_grp_id is not null
            and FB.EVNT_TYP_CDE in ('planShared', 'firmPublishedImmediately') then 'Y'
         when   FG.eods_grp_id is not null
            and FB.EVNT_TYP_CDE is null then 'N'
         when   FG.eods_grp_id is not null
            and FB.EVNT_TYP_CDE in ('accessRevoked', 'factFinder', 'dataGathering', 'factFinderCompleted', 'dataGatheringCompleted','factFinderCanceled', 'firmCreatedConsolidated')
            and FB.EVNT_TSTP > TRUNC(SYSDATE) - interval '90' day then 'N'
         else 'Y'
        end as GRP_FP_IND
       , FB.evnt_tstp
       , row_number() over (order by fb.evnt_tstp desc) as last_row
     from fp_grp    FG
       , fp_bus_evnt FB
       , grp     g
     where FG.fp_grp_id = FB.fp_grp_id
      and FG.eods_grp_id = g.eods_grp_id
      and g.GRP_CTG_CDE = '001'
      and g.GRP_INAC_DTE is null
      and g.prtt_cde = 'CGA_1'
      and FG.prtt_cde = 'FP_0'
      and FB.prtt_cde = 'FP_0'
      and g.eods_grp_id = '13193049')
 where last_row = 1;

hth

RogerT

So you do not need a grouping ... as you are not combining rows, you just need the one with the highest timestamp...

select distinct eods_grp_id
        ,GRP_FP_IND
        ,evnt_tstp
 from (select FG.eods_grp_id
       , EVNT_TYP_CDE
       , case
         when   FG.eods_grp_id is not null
            and FB.EVNT_TYP_CDE in ('planShared', 'firmPublishedImmediately') then 'Y'
         when   FG.eods_grp_id is not null
            and FB.EVNT_TYP_CDE is null then 'N'
         when   FG.eods_grp_id is not null
            and FB.EVNT_TYP_CDE in ('accessRevoked', 'factFinder', 'dataGathering', 'factFinderCompleted', 'dataGatheringCompleted','factFinderCanceled', 'firmCreatedConsolidated')
            and FB.EVNT_TSTP > TRUNC(SYSDATE) - interval '90' day then 'N'
         else 'Y'
        end as GRP_FP_IND
       , FB.evnt_tstp
       , row_number() over (order by fb.evnt_tstp desc) as last_row
     from fp_grp    FG
       , fp_bus_evnt FB
       , grp     g
     where FG.fp_grp_id = FB.fp_grp_id
      and FG.eods_grp_id = g.eods_grp_id
      and g.GRP_CTG_CDE = '001'
      and g.GRP_INAC_DTE is null
      and g.prtt_cde = 'CGA_1'
      and FG.prtt_cde = 'FP_0'
      and FB.prtt_cde = 'FP_0'
      and g.eods_grp_id = '13193049')
 where last_row = 1;

hth