DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Please give me a solution

  1. #1
    Join Date
    Aug 2002
    Posts
    176

    Post 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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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
  •  


Click Here to Expand Forum to Full Width