SQL Server Select Max of a Sum

Question

When I run this select

SELECT      
     D.Product_ID p_id
    ,D.Green_Sheet_ID gs_id
    ,SUM (P.prepress_amt) amt

   FROM  GS_Prepress AS P INNER JOIN
                      GS_Data AS D ON P.green_sheet_id = D.Green_Sheet_ID

   WHERE 
    Product_ID ='194456'  
   GROUP BY D.Product_ID, D.Green_Sheet_ID

I get this...

|p_id   | gs_id |amt      |
|-------|-------|---------|
|194456 | 5721  |33524.00 |
|194456 | 7484  |47524.00 |

I only want to select the row with the max(gs_is), so I only get this result?

|p_id   | gs_id |amt      |
|-------|-------|---------|
|194456 | 7484  |47524.00 |

Add TOP(1) and ORDER BY properly

Miguel Prz
– Miguel Prz

2013-03-15 21:05:51 +00:00
Commented Mar 15, 2013 at 21:05 — Miguel Prz
– Miguel Prz, Commented Mar 15, 2013 at 21:05

Matt Busche · Accepted Answer · 2013-03-15 21:08:04Z

5

Order the results and take TOP(1):

SELECT TOP(1)
    D.Product_ID p_id,
    D.Green_Sheet_ID gs_id,
    SUM (P.prepress_amt) amt
FROM
    GS_Prepress AS P
INNER JOIN
    GS_Data AS D ON P.green_sheet_id = D.Green_Sheet_ID
WHERE 
    Product_ID ='194456'  
GROUP BY
    D.Product_ID, D.Green_Sheet_ID
ORDER BY
    gs_id DESC

edited Mar 15, 2013 at 21:08

Matt Busche

14.4k5 gold badges38 silver badges63 bronze badges

answered Mar 15, 2013 at 21:04

MarcinJuraszek

126k15 gold badges199 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Indi Over a year ago

I think it should be order by gs_id desc

Martin Smith Over a year ago

@Kaf - The title says max of a sum though.

MarcinJuraszek Over a year ago

Right, wrong order column... But the title is Max of sum... Thanks!

Indi Over a year ago

@MartinSmith: not a big issue :D

user2175554 Over a year ago

Sorry I forgot to say that I need to be able to comment out my product_ID so I will have many product_id's with multiple D.Green_Sheet_ID's and I just need the max D.Green_Sheet_ID row.

Matt Busche · Accepted Answer · 2013-03-15 21:06:48Z

1

If you're going to use this query to ever pull more than one Product_ID then this will only return the relevant results. If your end result is only to have one item returned then use Marcin's answer.

SELECT D.Product_ID p_id
, D.Green_Sheet_ID gs_id
,SUM (P.prepress_amt) amt
FROM  GS_Prepress AS P 
   INNER JOIN GS_Data AS D ON P.green_sheet_id = D.Green_Sheet_ID
   INNER JOIN (SELECT MAX(Green_Sheet_ID) AS gs_ID
               FROM GS_Date
               GROUP BY Product_ID) G ON G.Gs_ID = D.Green_Sheet_ID

WHERE Product_ID ='194456'  
GROUP BY D.Product_ID, D.Green_Sheet_ID

answered Mar 15, 2013 at 21:06

Matt Busche

14.4k5 gold badges38 silver badges63 bronze badges

Collectives™ on Stack Overflow

SQL Server Select Max of a Sum

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related