Choose the maximum value from returning request



  • For example:

    SELECT 
           r.[name],  
           Sum(oa.premium) premSum 
           ,(r.[coefficient] * vt.coefficient) as coeff
    FROM 
    [o_application] oa      
    INNER JOIN [vehicle_policy] vp     (nolock) on oa.id = vp.[ogpo_application_id]
    INNER JOIN [region_registration] r (nolock) on vp.[region_registration] = r.id
    inner join [vehicle_type] vt       (nolock) on vt.id = vp.[vehicle_type]
    WHERE 
    (oa.policy_date between '2016-04-01' and '2016-06-06') 
    and vp.state_number = '123' 
    group by  r.[name], r.[coefficient], vt.coefficient, 
    vp.state_number
    order by coeff 
    

    Ask him back:

    name                prem        coef    number
    Акмолинская область 52399.00    2,7588  B694WMA
    Акмолинская область 14675.00    5,2536  B694WMA
    Акмолинская область 12841.00    2,7588  B694WMA
    

    If the number(number) is single, only one recording with a maximum coefficient should be returned. In this case:

    Акмолинская область 14675.00    5,2536  B694WMA
    

    How do you choose the maximum value?



  • Is that okay?

    select name, premSum, coeff, state_number from (
    SELECT 
           r.[name],  
           Sum(oa.premium) premSum 
           ,(r.[coefficient] * vt.coefficient) as coeff
    ,row_number() over(partition by r.[name], vp.state_number order by r.[coefficient] * vt.coefficient desc) rn
    FROM 
    [o_application] oa      
    INNER JOIN [vehicle_policy] vp     (nolock) on oa.id = vp.[ogpo_application_id]
    INNER JOIN [region_registration] r (nolock) on vp.[region_registration] = r.id
    inner join [vehicle_type] vt       (nolock) on vt.id = vp.[vehicle_type]
    WHERE 
    (oa.policy_date between '2016-04-01' and '2016-06-06') 
    and vp.state_number = '123' 
    group by  r.[name], r.[coefficient], vt.coefficient, 
    vp.state_number
    ) X where rn=1
    order by coeff 
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2