SQL Server : get columns based on MAX(SUM()) of other column

Question

I've ran into trouble trying to select columns based on the max of a sum of a column. I'm using SQL Server.

I have the following three tables (simplified)

DimensionTable

SupplierID	ProductID	Revenue
1	1	500
1	2	2000
1	3	3000
2	4	500
2	5	700
2	6	900
3	7	300
3	8	400
3	9	500

ProductTable

ProductID	ProductCategory
1	Category1
2	Category1
3	Category2
4	Category1
5	Category3
6	Category3
7	Category4
8	Category4
9	Category2

SupplierTable

SupplierID	SupplierNo
1	102030
2	203040
3	304050

What I would like to do is select SupplierNo and ProductCategory based on the highest max sum of the column Revenue, for each Supplier.

I think I have the "first level" of this query down, but need help with actually filtering rows where Revenue isn't MAX(SUM)). Right now, it is returning MAX(SUM(Revenue)) but grouped by both SupplierNo and ProductCategory.

Query currently is:

WITH dim AS
(
    SELECT
        ProductID,
        SupplierID,
        SUM(Revenue) AS sumRevenue
    FROM
        DimensionTable
    GROUP BY
        ProductID, SupplierID
),
supp AS
(
    SELECT
        SupplierID,
        SupplierNo
    FROM
        SupplierTable
),
prod AS
(
    SELECT
        ProductID,
        ProductCategory
    FROM
        ProductTable
)
SELECT
    MAX(t1.sumRevenue) AS maxSumRevenue,
    t2.SupplierNo,
    t3.ProductCategory
FROM
    dim t1
LEFT JOIN
    supp t2 ON t1.SupplierID = t2.SupplierID
LEFT JOIN
    prod t3 ON t1.ProductID = t3.ProductID
GROUP BY
    t2.SupplierNo, t3.ProductCategory
ORDER BY 
    MAX(t1.sumRevenue) DESC;

Desired result:

SupplierNo	ProductCategory	MAX(SUM(Revenue))
102030	Category2	3000
203040	Category3	1600
304050	Category4	700

So for each distinct SupplierNo, I want the ProductCategory with the highest value of MAX(SUM(Revenue)), and I want all three columns returned from the query.

score 2 · Accepted Answer · answered Mar 07 '22 at 08:44

You can use ROW_NUMBER() for your need, your query would be like :

SELECT *
FROM (
    SELECT SupplierNo
        ,ProductCategory
        ,SUM(Revenue) AS Revenue
        ,ROW_NUMBER() OVER (
            PARTITION BY SupplierNo ORDER BY SUM(Revenue) DESC
            ) AS rn
    FROM DimensionTable d
    INNER JOIN ProductTable p ON d.ProductID = p.ProductID
    INNER JOIN SupplierTable s ON s.SupplierID = d.SupplierID
    GROUP BY SupplierNo
        ,ProductCategory
    ) a
WHERE rn = 1

SQL Server : get columns based on MAX(SUM()) of other column

1 Answers1