selecting max value from table with two variable colums (microsoft SQL)

i´m working with a table that looks like this:

Start https://i.stack.imgur.com/uibc3.png

My desired result would look like this:

Result https://i.stack.imgur.com/v0sic.png

So i´m triyng to select the max value from two “combined” colums. If the values are the same amount (Part C), the outcome doesn’t matter. I tried to order the table by max value and then using distinct but the result didn’t turn out as expected

Could you please offer a solution or some insight to this? Thanks in advance!

Answer

Use row_number():

select *
from (
    select t.*, row_number() over(partition by part order by amount desc, zone) rn
    from mytable t
) t
where rn = 1

For each part, this gives you the row with the highest amount; if there are top ties, column zone is used to break them.

If you want to allow ties, then use rank() instead, like:

rank() over(partition by part order by amount desc) rn