MS SQL Server select rows with max values

Question

Good day

I have Table1:

COLUMN1    COLUMN2   Column3  
----------------------------
Eva           Apple       1
Eva           Apple       2
Eva           Apple       3
Eva           Apple       4
Eva           Apple       5
Eva           Apple       6
Bob           Apple       1
Bob           Samsung     1
Bob           Samsung     2
...           ...        ...

I need

COLUMN1    COLUMN2   Column3
----------------------------
Eva           Apple       6
Bob           Samsung     2
Bob           Apple       1
...           ...        ...

How i can setup string for select only rows with MAX values in Column3 ?

My version of string is :

SELECT MAX(Column3) , [column2], [Column2] 
FROM Table1
WHERE Column3 =  MAX ;

Thanks for Opinions

Answer 1

You can use row_number

Select top (1) with ties *
   from table1 
   order by row_number() over (partition by Column1, Column2 order by Column3 desc)

Other way is to use outer query:

Select * from (
   Select *, RowN = row_number() over (partition by Column1, Column2 order by Column3 desc) from table1 ) a
   Where a.RowN = 1