Get the Defect Type with the maximum Total Defect Qty

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 :

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 1

A simple option uses with ties:

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

The downside is that this does not let you control the ordering of the resultset. If you really need that feature, then use a subquery:

SELECT *
FROM (
    SELECT
        [Defect Type],
        YEAR([Date]) AS YearOfDefect,
        SUM([Total Defect Qty]) AS [Sum],
         RANK() OVER(PARTITION BY YEAR([Date]) ORDER BY SUM([Total Defect Qty]) DESC) rn
    FROM SupplierQuality
    GROUP BY [Defect Type], YEAR([Date])
) t
WHERE rn = 1
ORDER BY YearOfDefect