SQL query cubes and roll up

Question

Currently trying to find the max, For chicago only computers SHOULD show up because it's quantity is 1010 it being the largest quanitity

This below is the query that is performed on the above cube. I am just tring to find the max quanity for product name per each city.

SELECT [Supplier City], [Product Name], [Total Transactions Quantity]
FROM Tb_Final_Cube
WHERE "Supplier Name" IS NULL
AND "Supplier City" IS NOT NULL
AND "Supplier State" IS NOT NULL
AND "Product Packaging" IS NOT NULL
AND "Product Name" IS NOT NULL
AND "Product Category" IS NULL
AND "Product Line" IS NULL

Answer 1

Use window functions:

SELECT tfb.*
FROM (SELECT [Supplier City], [Product Name], [Total Transactions Quantity],
             ROW_NUMBER() OVER (PARTITION BY [Product Name] ORDER BY [Total Transactions Quantity] DESC) as seqnum
      FROM Tb_Final_Cube
      WHERE "Supplier Name" IS NULL AND
            "Supplier City" IS NOT NULL AND
            "Supplier State" IS NOT NULL AND
            "Product Packaging" IS NOT NULL AND
            "Product Name" IS NOT NULL AND
            "Product Category" IS NULL AND
            "Product Line" IS NULL 
      ) tfb
WHERE seqnum = 1;