Group rows with a value depending on condition of a different column SQL

I have a list of rows that I need to group by a key and for each group the value of two columns depends on a condition from a different column.

For example

should become

That means, for the value of Col 5 take the one where col 4 is the lowest and for the col 7 take the value where col 6 is the highest.

Any idea how to achieve this result?

Answer

This assumes that your group is determined by your key column

;with r as (
    select Key, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8,
    row_number() over(partition by key order by Col4) as c4,
    row_number() over(partition by key order by Col6 desc) as C6
)
select distinct r1.Key, r1.Col1, r1.Col2, r1.Col3, 
    r2.col4, r2.col5, 
    r3.col6, r3.col7, 
    r1.col8
from r r1
join r r2 on r2.key=r1.key and r2.c4=1
join r r3 on r3.key=r1.key and r3.c6=1