CODEWITHSUNDEEP
sqlsql-server
K.Badra
K.Badra

Reputation: 25

How can I SELECT rows with MAX(Column value)

SELECT        
    DATEPART(YEAR, tblGRN.GRNApproveDate) AS Year, 
    tblGRNMaterials.MaterialCode, 
    tblMaterial.MaterialDescription, 
    SUM(tblGRNMaterials.NetQty) AS Qty
FROM
    tblGRN 
INNER JOIN
    tblGRNMaterials ON tblGRN.GRNNO = tblGRNMaterials.GRNNO 
INNER JOIN
    tblMaterial ON tblGRNMaterials.MaterialCode = tblMaterial.MaterialCode
WHERE
    (tblGRN.GRNStoreID = 'RM_Main') 
    AND (tblGRN.GRNStatus = 1) 
    AND (CONVERT(DATE, tblGRN.GRNApproveDate) BETWEEN '2017-01-01' AND '2018-12-31')
GROUP BY 
    tblGRNMaterials.MaterialCode, tblMaterial.MaterialDescription, 
    DATEPART(YEAR, tblGRN.GRNApproveDate)
ORDER BY 
    tblGRNMaterials.MaterialCode

My output

Year    MaterialCode    MaterialDescription         Qty
-------------------------------------------------------------
2017    LM/CCM/SO       Cellulose C. Methyl       200.000
2018    LM/CCM/SO       Cellulose C. Methyl       350.000
2017    LM/MAG/PW       Magnesium Stearate        175.000
2018    LM/MAG/PW       Magnesium Stearate        250.000
2017    LM/MCC/PW       Micro Crystal Cellulose    75.000
2018    LM/MCC/PW       Micro Crystal Cellulose     0.320

I need to select each year holding the maximum value of 'Qty', comparison with year 2017 and 2018

Result should be:

Year    MaterialCode    MaterialDescription         Qty
----------------------------------------------------------
2018    LM/CCM/SO       Cellulose C. Methyl       350.000
2018    LM/MAG/PW       Magnesium Stearate        250.000
2017    LM/MCC/PW       Micro Crystal Cellulose    75.000

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

First, I would write the query like this:

SELECT year(g.GRNApproveDate) AS Year, gm.MaterialCode, 
       m.MaterialDescription, SUM(gm.NetQty) AS Qty
FROM tblGRN g INNER JOIN
     tblGRNMaterials gm
     ON g.GRNNO = gm.GRNNO INNER JOIN
     tblMaterial m
     ON gm.MaterialCode = m.MaterialCode
WHERE g.GRNStoreID = 'RM_Main' AND
      g.GRNStatus = 1 AND
      g.GRNApproveDate >= '2017-01-01' AND
      g.GRNApproveDate < '2019-01-01'
GROUP BY gm.MaterialCode, m.MaterialDescription, year(g.GRNApproveDate)
ORDER BY gm.MaterialCode;

Although not the most performant method, probably the simplest is:

SELECT TOP (1) WITH TIES, year(g.GRNApproveDate) AS Year, gm.MaterialCode, 
       m.MaterialDescription, SUM(gm.NetQty) AS Qty
FROM tblGRN g INNER JOIN
     tblGRNMaterials gm
     ON g.GRNNO = gm.GRNNO INNER JOIN
     tblMaterial m
     ON gm.MaterialCode = m.MaterialCode
WHERE g.GRNStoreID = 'RM_Main' AND
      g.GRNStatus = 1 AND
      g.GRNApproveDate >= '2017-01-01' AND
      g.GRNApproveDate < '2019-01-01'
GROUP BY gm.MaterialCode, m.MaterialDescription, year(g.GRNApproveDate)
ORDER BY ROW_NUMBER() OVER (PARTITION BY gm.MaterialCode ORDER BY SUM(gm.NetQty) DESC);

Upvotes: 1

Related Questions