Find the type of goods that takes up the most space in the warehouse

Question

I have query like this

select StockId, ProductType, sum(ProductVolume) as ProductTypeVolume
from myTable
where InStock = 1
group by StockId, ProductType

with result like this

StockId	ProductType	ProductTypeVolume
10	Type1	65
10	Type2	25
10	Type3	45
20	Type2	80
20	Type4	60
20	Type5	20

I need to get a result where there will be two rows, one for each StockId, with the largest ProductTypeVolume like this

StockId	ProductType	ProductTypeVolume
10	Type1	65
20	Type2	80

Answer 1

You need CTE, subquery and group by:

WITH t
     AS (SELECT stockid,
                producttype,
                Sum(productvolume) AS ProductTypeVolume
         FROM   mytable
         WHERE  instock = 1
         GROUP  BY stockid,
                   producttype)


SELECT A.stockid,
       B.producttype,
       A.producttypevolume
FROM   (SELECT stockid,
               Max(producttypevolume) ProductTypeVolume
        FROM   t
        GROUP  BY stockid) A
       JOIN t B
         ON A.stockid = B.stockid
            AND A.producttypevolume = B.producttypevolume  

You can use row_number as follows

SELECT TOP(2) stockid,
              producttype,
              producttypevolume
--,ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY ProductTypeVolume DESC)
FROM   t
ORDER  BY Row_number()
            OVER (
              partition BY stockid
              ORDER BY producttypevolume DESC) ASC