There is a 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

The request returns:

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

It is necessary if the numbers (number) are lonely, then return only one entry whose maximum coefficient. In this case :

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

How to choose the maximum value?

    2 answers 2

    So fit?

     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
    • yes, but if in partition by only by vp.state_number - Zhandos
     SELECT * FROM ( SELECT r.[name], Sum(oa.premium) premSum ,(r.[coefficient] * vt.coefficient) as coeff ,MAX(r.[coefficient] * vt.coefficient) OVER ( PARTITION BY vp.state_number) as MAXcoeff 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 ) q where q.MAXcoeff = q.coeff