with max_item_id as (select item_id ( select item_id,sum(amt) as sum_amt,rom_number() over(order by sum(amt) desc ) rn from table_a where to_date(dt,'yyyymmdd')>=to_date('20180701','yyyymmdd') and to_date(dt,'yyyymmdd')<=to_date('20180931','yyyymmdd') and a.seller_id='123' group by item_id )t where t.rn=1 ) select item_id,buyer_id from ( select a.item_id,a.buyer_id,sum(a.amt) sum_buyer_id,rom_number() over(order by sum(a.amt) desc ) rn from table_a a join max_item_id b on a.item_id=b.item_id where to_date(dt,'yyyymmdd')>=to_date('20180701','yyyymmdd') and to_date(dt,'yyyymmdd')<=to_date('20180931','yyyymmdd') and a.seller_id='123' group by a.item_id,a.buyer_id ) t where t.rn=1 ;