How can I SELECT rows with MAX(Column value)

Question

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

Hi. Please, add sample data

Backs
– Backs

2019-01-03 03:20:34 +00:00
Commented Jan 3, 2019 at 3:20 — Backs
– Backs, Commented Jan 3, 2019 at 3:20
'My out put' is my basic data output

K.Badra
– K.Badra

2019-01-03 03:22:24 +00:00
Commented Jan 3, 2019 at 3:22 — K.Badra
– K.Badra, Commented Jan 3, 2019 at 3:22

Gordon Linoff · Accepted Answer · 2019-01-03 03:23:37Z

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);

Collectives™ on Stack Overflow

How can I SELECT rows with MAX(Column value)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related