query results

I have a query wchich does not return the required results:
select s.ptcpt_cd

      ,to_char(nmarket.xmkt_all_datetime.gmt_to_local('CCG','MW', (c.end_dt_gmt) , 'EST', 1), 'MM/DD/YYYY') Start_date

      ,dt.sttl_item_nme charge_type_nme

      ,ct.sttl_comp_desc || '_' || to_char(nmarket.xmkt_all_datetime.gmt_to_local('CCG','MW', (c.end_dt_gmt), 'EST', 1), 'MM/DD/YYYY') || '_' ||

       x.xp_nme || '_' || substr(dt.sttl_item_nme, 0, 2) || nvl2(c.biltrl_iso_ref, '_' || c.biltrl_iso_ref, '') external_trade_id

      ,decode(to_char(nmarket.xmkt_all_datetime.gmt_to_local('CCG','MW', (d.start_dt_gmt), 'EST', 1), 'HH24'),

            '00',

            '24',

            to_char(nmarket.xmkt_all_datetime.gmt_to_local('CCG','MW', (d.start_dt_gmt), 'EST', 1), 'HH24')) load_start_time_code

      ,decode(to_char(nmarket.xmkt_all_datetime.gmt_to_local('CCG','MW', (d.end_dt_gmt), 'EST', 1), 'HH24'),

            '00',

            '24',

            to_char(nmarket.xmkt_all_datetime.gmt_to_local('CCG','MW', (d.end_dt_gmt), 'EST', 1), 'HH24')) load_end_time_code

      ,decode(dt.sttl_item_nme, 'RT Virtual Energy Amount',c.num_val*-1,

            'RT Asset Energy Amount', decode(ct.sttl_comp_desc, 'DA Asset Schedule Volume', c.num_val*-1, c.num_val),

            'DA Non-Asset Energy Amount', decode(ct.sttl_comp_desc, 'DA Physical Sell Trans Vol at Non Asset', c.num_val*-1, c.num_val),

            'RT Non-Asset Energy Amount', decode(ct.sttl_comp_desc, 'RT Physical Sell Trans Vol at Non Asset', c.num_val*-1, c.num_val),

            c.num_val) quantity

      ,0 cash_payment

      ,x.xp_nme delivery_point

      ,s.revised_dt_sys

from nmarket.nm_settlement s

    ,nmarket.nm_sttl_item_dtl d

    ,nmarket.nm_sttl_component c

    ,nmarket.nm_xp x

    ,nmarket.nm_sttl_item_dtl_type dt

    ,nmarket.nm_sttl_component_type ct

where s.ptcpt_cd = 'CCG'

and s.source_cd = 'ESTIM'

and s.start_dt_gmt between to_date('02-AUG-2008 0500', 'DD-MON-YYYY HH24MI')

                       and to_date('03-AUG-2008 0400', 'DD-MON-YYYY HH24MI')

and s.revised_dt_sys between to_date('02-AUG-2008 0500', 'DD-MON-YYYY HH24MI') - 20

                         and to_date('03-AUG-2008 0400', 'DD-MON-YYYY HH24MI') + 20

and s.sttl_id = d.sttl_id

and d.start_dt_gmt between to_date('02-AUG-2008 0500', 'DD-MON-YYYY HH24MI')

                       and to_date('03-AUG-2008 0400', 'DD-MON-YYYY HH24MI')

and (

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'DA Asset Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'DA Asset Schedule Volume')) or

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'DA Virtual Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'DA Net Virtual Schedule Volume')) or

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'RT Virtual Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'DA Net Virtual Schedule Volume')) or

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'RT Asset Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'DA Asset Schedule Volume')) or  

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'RT Asset Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'RT Metered Billable Volume')) or      

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'DA Non-Asset Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'DA Physical Sell Trans Vol at Non Asset')) or      

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'DA Non-Asset Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'DA Physical Buy Trans Vol at Non Asset')) or

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'RT Non-Asset Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'RT Physical Sell Trans Vol at Non Asset')) or      

     (d.sttl_item_num in (select sttl_item_num from nm_sttl_item_dtl_type where sttl_item_nme = 'RT Non-Asset Energy Amount') and

      c.sttl_comp_num in (select sttl_comp_num from nm_sttl_component_type where sttl_comp_desc = 'RT Physical Buy Trans Vol at Non Asset'))    

    )                        

and d.sttl_item_num = dt.sttl_item_num

and s.sttl_id = c.sttl_id

and c.sttl_comp_num = ct.sttl_comp_num

and d.start_dt_gmt = c.start_dt_gmt

and c.xp_id = x.xp_id

and x.end_dt_gmt = '31-DEC-4000'

and x.inactive_dt_sys = '31-DEC-4000'

and x.xp_nme = 'AMIL.CCG'

order by 7,1,4
The rows returned are the same with the exception of revised_dt_sys.  I only want the record with the max revised_dt_sys for the rows that are duplicate.  Any idea how to do this?
thanks a lot again .Start Free Trial