All Forums Database
Jay2014 3 posts Joined 01/14
26 Jan 2015
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
 

Jay2014 3 posts Joined 01/14
26 Jan 2015

Please help me on this !!

Raja_KT 1246 posts Joined 07/09
26 Jan 2015

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Jay2014 3 posts Joined 01/14
27 Jan 2015

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
 
 

You must sign in to leave a comment.