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

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

CodePudding user response：

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