CODEWITHSUNDEEP
oracle-database
Santosh Kumar
Santosh Kumar

Reputation: 69

how can eliminate value in oracle with latest record

    i will get latest record by timestamp but it is not filter desired

output here is code i was try to get latest record bu it is not work me.i will attach a image for better understanding.can you figure out that problems enter image description here 

    select *
    from (select
       id,NPCI_REFMSGID,TO_CHAR (created_date, 'dd.mm.rrrr hh24:mi:ss.ff3')AS created_Date, 
      rank() over (partition by ID order by TO_CHAR (created_date, 'dd.mm.rrrr hh24:mi:ss.ff3') desc) r
      from emnd_tblemandate_mst
      --where end_enrollment_date is null
    )
    where r = 1 AND NPCI_REFMSGID='1ea345bc63644b53a88076040ef979e3'

Upvotes: 0

Views: 39

Answers (1)

Thomas Strub
Thomas Strub

Reputation: 1285

You are partitioning by ID so you get all rows in your result. Think it should be

select *
from (select id 
           , NPCI_REFMSGID
           , TO_CHAR (created_date, 'dd.mm.rrrr hh24:mi:ss.ff3')AS created_Date
           , rank() over (partition by NPCI_REFMSGID order by created_date desc) r
      from   emnd_tblemandate_mst
)
where r = 1 
AND   NPCI_REFMSGID = '1ea345bc63644b53a88076040ef979e3'

Then you get only the newest record for a specific NPCI_REFMSGID

Upvotes: 1

Related Questions