How to check in which category each employee has sold the most goods? I can display only employee's name and sold goods

Question

The whole table

USE Northwind
SELECT MAX(TotalOrder)


FROM vwEmployesAndMostSoldCategories
GROUP MAX(TotalOrder)

What I am only able to output

USE Northwind
SELECT
   FullName
   , MAX(TotalOrder) AS TheMaxSoldUnits
FROM vwEmployesAndMostSoldCategories
GROUP BY FullName

Answer 1

You could use a TOP query here:

WITH cte AS (
    SELECT FullName, CatgegoryName,
           SUM(TotalOrder) AS SumTotalOrder,
           ROW_NUMBER() OVER (PARTITION BY FullName
                              ORDER BY SUM(TotalOrder) DESC) rn
    FROM vwEmployesAndMostSoldCategories
    GROUP BY FullName, CategoryName
)

SELECT FullName, CategoryName, SumTotalOrder AS TotalOrder
FROM cte
WHERE rn = 1;

If a given employee might be tied for having two or more categories with the same order total, and you want to show all ties, then replace ROW_NUMBER, with RANK.