Select values by type with maximum value-CodePudding

There is a table

Type	Subtype	Quantity
T1	PT1	3
T1	PT2	2
T2	PT11	3
T2	PT12	5

It is necessary to select from it the names of subtypes for each type with the largest number that is, it should turn out

Subtype
PT1
PT12

CodePudding user response：

One option is using WITH TIES in concert with the window function row_number() over ()

Select top 1 with ties
       Subtype
 From  YourTable
 Order By row_number() over (partition by [type] order by quantity desc)

or you can use a subquery (a nudge more performant)

Select Subtype
 From  (
        Select *
              ,RN = By row_number() over (partition by [type] order by quantity desc)
         From YourTable 
       ) A
Where RN=1