Question: I have the following query which gets the sum of defect quantity per Defect Type :

```
SELECT 
[Defect Type]
,YEAR([Date]) AS YearOfDefect
,SUM([Total Defect Qty]) AS [Sum] FROM SupplierQuality
GROUP BY [Defect Type],YEAR([Date])

```

This is the result :

[![enter image description here](https://i.stack.imgur.com/PNbtp.png)](https://i.stack.imgur.com/PNbtp.png)

I want to have the defect type with the maximum sum of total defect quantity like below :

```
DefectType  YearOfDefect Sum    
No Impact   2019         586780230
No Impact   2018         437989564

```

Answer: 
Making the assumption I make in the comments, and this is per year, then you can use `ROW_NUMBER` to get the "top" row per group:

```sql
WITH CTE AS(
    SELECT [Defect Type],
           YEAR([Date]) AS YearOfDefect,
           SUM([Total Defect Qty]) AS [Sum]
    FROM SupplierQuality
    GROUP BY [Defect Type],
             YEAR([Date]))
SELECT TOP (1) WITH TIES
       [Defect Type],
       YearOfDefect,
       [Sum]
FROM CTE
ORDER BY ROW_NUMBER() OVER (PARTITION BY YearOfDefect ORDER BY [Sum] DESC);

```
Please also refer to another method：

```
with cte
as(SELECT 
[Defect Type]
,YEAR([Date]) AS YearOfDefect
,SUM([Total Defect Qty]) AS [Sum] FROM SupplierQuality
GROUP BY [Defect Type],YEAR([Date]))
,cte2 as
(select YearOfDefect,max([Sum]) [Sum] 
from cte 
group by YearOfDefect)

select c1.[Defect Type],c2.YearOfDefect,c2.[Sum] 
from cte c1
join c2
on c1.[Defect Type]=c2.[Defect Type] and c1.YearOfDefect=c2.YearOfDefect

```