In SQL how can I get only the first result per group

Question

I have a table that looks like the following:

Material	Branch	PRICE
A	B1	19.00
A	B2	17.00
A	B3	17.00
B	B1	20.00
B	B2	22.00
B	B3	19.00
C	B4	22.00
C	C3	19.00

And I'm trying to write a query that will return me only the first result in the list with the highest price. So for example what I would want return from my query is:

A B1 19.00

B B2 22.00

C B4 22.00

In case any values match i still only want the first result in the list. i am using MSSQL to write this query, can anyone help me? I have tried using row_number with a partition but am not seeing the results I am expecting.

Answer 1

With row number, this would be:

select t.*
from (select t.*,
             row_number() over (partition by material order by price desc) as seqnum
      from t
     ) t
where seqnum = 1;