-
Please give me a solution
Hi ,
I have a small query with 2 tables
Table sld_ac(i_cust_id,i_admin_dept_div,I_ac_id, d_cal)
Table sld_loan_iss(i_ac_id,a_loan)
select i_cust_id,i_admin_dept_div,sum(a_loan)
from sld_loan_iss a, sld_ac b
where a.i_ac_id = b.i_ac_id
and a.d_cal = to_date('2005-07-19','yyyy-mm-dd')
group by i_cust_id,i_admin_dept_div;
Sample Data From Above Query
I_cust_id i_admin_dept_ a_loan
1064 0002072 122923494.38
1066 0000177 171285315.39
1067 0000426 67352447.59
1067 0005480 4384484.18
I need the SQL to take the i_cust_id,I_admin_dept and sum(a_loan) where the sum(a_loan) is maximum for the i_cust_id. i.e I need only the records with the maximum sum(a_loan) for each i_cust_id but sum should grouped by both i_cust_id and i_admin_dept.
For example from the above result for i_cust_id 1067 only the record
1067 0000426 67352447.59 should be returned since it has the maximum in sum(a_loan).
Could anyone help us on this please.
Thanks
Rohit
Success Consists of Getting Up Just One More Time Than You've Fallen Down
Be Blessed
-
Hi
Code:
select temp.x,temp.y ,max(temp.z)
from (select i_cust_id x,i_admin_dept_div y,sum(a_loan) z
from sld_loan_iss a, sld_ac b
where a.i_ac_id = b.i_ac_id
and a.d_cal = to_date('2005-07-19','yyyy-mm-dd')
group by i_cust_id,i_admin_dept_div) temp
group by temp.x,temp.y
-----OR---
select i_cust_id,i_admin_dept_div,sum(a_loan)
from sld_loan_iss a, sld_ac b
where a.i_ac_id = b.i_ac_id
group by ld_loan_iss a, sld_ac b
having sum(a_loan)=(select max(sum(a_loan))
from sld_loan_iss a, sld_ac b
and
a.d_cal = to_date('2005-07-19','yyyy-mm-dd'
group by i_cust_id,i_admin_dept_div)
---------------OR-------------------------------
on 8i
select *
from (select i_cust_id,i_admin_dept_div,sum(a_loan) x
from sld_loan_iss a, sld_ac b
where a.i_ac_id = b.i_ac_id
and a.d_cal = to_date('2005-07-19','yyyy-mm-dd')
group by i_cust_id,i_admin_dept_div
order by x desc )
where rownume=1
regards
Hrishy
Last edited by hrishy; 09-09-2005 at 02:08 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|