Finding Difficulty in getting maximum value

Question

V_ABC(its a View)

ID        value   interest  Reference Code  

 1           5       Fixed             2
 1           2       Variable          4
 2           6       Variable          5
 2           2       Fixed             1
 3           4       Fixed             5
 3           1       Variable          4

i need this OutPut please.

ID    value     Interest      Reference Code
1      7           Fixed            4
2      8           Variable         5
3      5           Fixed            5

I have a view V_ABC. I am trying to add value which is fine and also getting Max reference Code. On Top, iIam trying to get interest type which based on maximum value but failed so far. e.g in view,

• ID 1 has maximum value 5 and need Interest Fixed
• ID 2 maximum value 6 and interest should be Variable
• ID 3 maximum value 4 and interest should be fixed .

I am trying to get interest. Here is my SCRIPT. I am using SQL server 2016

Select id,sum(value),Max(ReferenceCode)
(

Select id,value,

first_value(Interest) over (Partition by value Order by value desc) as Interest,Referencecode  From V_ABC

)dd

group by id

Answer 1

Probably the simplest method uses row_number() and conditional aggregation:

select id, sum(value),
       max(case when seqnum = 1 then interest end),
       max(case when seqnum = 1 then reference_code end)
from (select t.*,
             row_number() over (partition by id order by value desc) as seqnum
      from t
     ) t
group by id;

If you want to be fancy, you can use select distinct with window functions:

select distinct id,
       sum(value) over (partition by id),
       first_value(interest) over (partition by id order by value desc),
       first_value(reference_code) over (partition by id order by value desc)
from t;