How can I SELECT rows with MAX(Column value)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

0

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

sql sql-server

share|improve this question

edited Jan 3 at 6:29

marc_s

584k13011241270

asked Jan 3 at 3:18

K.BadraK.Badra

63

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi. Please, add sample data
  
  – Backs
  Jan 3 at 3:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  'My out put' is my basic data output
  
  – K.Badra
  Jan 3 at 3:22
  

  
  
  
  

add a comment | 

0

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

sql sql-server

share|improve this question

edited Jan 3 at 6:29

marc_s

584k13011241270

asked Jan 3 at 3:18

K.BadraK.Badra

63

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi. Please, add sample data
  
  – Backs
  Jan 3 at 3:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  'My out put' is my basic data output
  
  – K.Badra
  Jan 3 at 3:22
  

  
  
  
  

add a comment | 

0

0

0

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

sql sql-server

share|improve this question

edited Jan 3 at 6:29

marc_s

584k13011241270

asked Jan 3 at 3:18

K.BadraK.Badra

63

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

sql sql-server

sql sql-server

share|improve this question

edited Jan 3 at 6:29

marc_s

584k13011241270

asked Jan 3 at 3:18

K.BadraK.Badra

63

share|improve this question

edited Jan 3 at 6:29

marc_s

584k13011241270

asked Jan 3 at 3:18

K.BadraK.Badra

63

share|improve this question

share|improve this question

edited Jan 3 at 6:29

marc_s

584k13011241270

edited Jan 3 at 6:29

marc_s

584k13011241270

edited Jan 3 at 6:29

marc_s

584k13011241270

584k13011241270

asked Jan 3 at 3:18

K.BadraK.Badra

63

asked Jan 3 at 3:18

K.BadraK.Badra

63

asked Jan 3 at 3:18

K.BadraK.Badra

63

63

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi. Please, add sample data
  
  – Backs
  Jan 3 at 3:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  'My out put' is my basic data output
  
  – K.Badra
  Jan 3 at 3:22
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi. Please, add sample data
  
  – Backs
  Jan 3 at 3:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  'My out put' is my basic data output
  
  – K.Badra
  Jan 3 at 3:22
  

  
  
  
  

Hi. Please, add sample data

– Backs
Jan 3 at 3:20

Hi. Please, add sample data

– Backs
Jan 3 at 3:20

'My out put' is my basic data output

– K.Badra
Jan 3 at 3:22

'My out put' is my basic data output

– K.Badra
Jan 3 at 3:22

add a comment | 

1 Answer
1

active

oldest

votes

1

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

share|improve this answer

answered Jan 3 at 3:23

Gordon LinoffGordon Linoff

794k37318421

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54015889%2fhow-can-i-select-rows-with-maxcolumn-value%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

1

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

share|improve this answer

answered Jan 3 at 3:23

Gordon LinoffGordon Linoff

794k37318421

add a comment | 

1

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

share|improve this answer

answered Jan 3 at 3:23

Gordon LinoffGordon Linoff

794k37318421

add a comment | 

1

1

1

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

share|improve this answer

answered Jan 3 at 3:23

Gordon LinoffGordon Linoff

794k37318421

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

share|improve this answer

answered Jan 3 at 3:23

Gordon LinoffGordon Linoff

794k37318421

share|improve this answer

share|improve this answer

answered Jan 3 at 3:23

Gordon LinoffGordon Linoff

794k37318421

answered Jan 3 at 3:23

Gordon LinoffGordon Linoff

794k37318421

answered Jan 3 at 3:23

Gordon LinoffGordon Linoff

794k37318421

794k37318421

add a comment | 

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54015889%2fhow-can-i-select-rows-with-maxcolumn-value%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here