Re: Can someone simplify this query

From: Sagaran <sagaran.kasturi_at_gmail.com>
Date: 9 Feb 2005 23:22:29 -0800
Message-ID: <1108020149.122467.327600@o13g2000cwo.googlegroups.com>

I realised that after posting. Please use this one.

 select * from
   (select a.officecode off1, a.acno_code acc1,

      sum(current_purchase_amount) over (partition by a.office_code,  a.acno_code) as sum1, rank () over (partition by a.office_code, a.acno_code) rno,

      b.officecode off2, b.account_code acc2,
      sum(org_budget_amt) over (partition by b.account_code,
 b.office_code) as sum3,
      sum(rev_budget_amt) over (partition by b.account_code,

 b.office_code) as sum4 , rank() over (partition by b.account_code, b.office_code) rowno
 from bweb_assetpurchasetxndetails a, bweb_budgetmaster b  where a.officecode (+) = b.officecode
 and a.acno_code (+) = b.account_code)
where rno =1 and rowno=1;

This is the same one as before with a change that I am getting a rank for each row in the inner query and using that to pick only one row in the outer one. Received on Thu Feb 10 2005 - 01:22:29 CST