Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Analytical function to get latest record

verde1030Sep 6 2017 — edited Sep 20 2017

Hi all, still learning oracle analytical functions and hoping to get some help with the below issue

I have a dataset that looks like the below. i.e for the same Master_ID i have multiple pol_id's across different dates.  I am creating a report and requirement is to create a 'new column 'that shows only the pol_id of the latest 'eff_dt' record for both rows.

Current Dataset:

with t as

( select '81WE6678' as pol_id, '99WC2003' as prev_pol_id, '11060243' as Master_GID, '1/23/2017' as Eff_dt from dual Union all

select '99WC2003' as pol_id, '99WC2003' as prev_pol_id, '11060243' as Master_GID, '1/23/2016' as Eff_dt  from dual)

select pol_id, prev_pol_id, Master_GID, Eff_dt  from t

POL_IDPREV_POL_IDMASTER_GIDEFF_DT
81WE667899WC2003110602431/23/2017
99WC200399WC2003110602431/23/2016 

Expected Result is to add a new column called Latest_pol_Id and populate the older record(2016 eff_dt record) with the pol_id of the newer record(2017 eff_dt record)

POL_IDPREV_POL_IDMASTER_GIDEFF_DTLATEST_POL_ID
81WE667899WC2003110602431/23/201781WE6678
99WC200399WC2003110602431/23/201681WE6678

with t as

( select '81WE6678' as pol_id, '99WC2003' as prev_pol_id, '11060243' as Master_GID, '1/23/2017' as Eff_dt, '81WE6678' as Latest_Pol_id from dual Union all

select '99WC2003' as pol_id, '99WC2003' as prev_pol_id, '11060243' as Master_GID, '1/23/2016' as Eff_dt,  '81WE6678' as Latest_Pol_id from dual)

select pol_id, prev_pol_id, Master_GID, Eff_dt, Latest_Pol_id from t

https://livesql.oracle.com/apex/f?p=590:1:11557525977646:::::

Any ideas are appreciated. 

Thanks a lot.

B

This post has been answered by mathguy on Sep 6 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 18 2017
Added on Sep 6 2017
3 comments
1,732 views