Group By and it's Highest Value using SQL Query

Question

In the table below, how do I group by Unit and then get the highest value from Quantity of that group? I have put 21 in row 4 under HighestValue column since its the highest value among all records in Unit

Answer 1

Check the queries that produce results in different forms:

select *, max(Quantity) over (partition by Unit) as HighestValue
from MyTable

select Unit, max(Quantity) as HighestValue
from MyTable
group by Unit