keep the data last recod of the groupp

Database

keep the data last recod of the groupp

I am trying to keep value at end of the group rest of the values should be null in the group.

Input data

Mbr_num  class_nmae year  Member_total

123        abc         2013    6000

123        abc         2013    6000

123        xyz         2013    6000

123       yxz        2013      6000

123      abc         2014      5000

123      abc        2014        5000

123     xyz         2014        5000

123     yxz       2014        5000

expected output

Mbr_num class_nmae year Member_total

123 abc 2013

123 abc 2013 6000

123 xyz 2013

123 yxz 2013 6000

123 abc 2014

123 abc 2014

123 abc 2014 5000

123 xyz 2014

123 yxz 2014 5000

I need partion the data by class_name and Year and keep the data last recod of the groupp

3 REPLIES

Re: keep the data last recod of the groupp

Please help me on this !!

Enthusiast

Re: keep the data last recod of the groupp

I am assuming that the ones in bold are the required (ones). How do you get the 5,6 lines. I am thinking that the 6th line is 123 abc 2014 5000.

Try something thus:

select Mbr_num, class_nmae, year1, Member_total from your_tablemmm  order by year1,class_nmae

qualify row_number()over(partition by class_nmae,year1 

order by year1,class_nmae desc)=1

Re: keep the data last recod of the groupp

Hi Raj, Thanks for Your response!!

I want see all rows in result table not just bolded , I want keep the value in the last row of the group / partition ,rest of the rows in that group / partition should replace with 'null' or '0' .

I tried with below query but   job got failed with spool space error in my account.

Sel  claims.*,

,count(*) OVER (PARTITION BY EXTRACT(YEAR FROM a.Svc_dt) ORDER BY EXTRACT(YEAR FROM a.Svc_dt) ) AS MM_ROW_NUM

,count(*) OVER (PARTITION BY EXTRACT(YEAR FROM Svc_dt)

ORDER BY EXTRACT(YEAR FROM Svc_dt) ) AS MM_ROW_COUNT,

case when MM_ROW_NUM <> MM_ROW_COUNT then 0 else MEMBERS_COUNT end as MEMBERS_COUNT

case when MM_ROW_NUM <> MM_ROW_COUNT then 0 else INTEGRATED_MEM end as INTEGRATED_MEM,

case when MM_ROW_NUM <> MM_ROW_COUNT then 0 else MEMBERS_COUNT end as CURVOUT_MEM

from Claims left join MM_month

on year =year

123 abc 2013 0

123 abc 2013 6000

123 xyz 2013 0

123 yxz 2013 6000

123 abc 2014 0

123 abc 2014 0

123 abc 2014 5000

123 xyz 2014 0

123 yxz 2014 5000