Return Entire Row which has the first Max Value in One Field

Question

I have the following data set that I'm getting using this query or subquery:

SELECT  prodMPEntity, 
    prodMPLiquid,
    prodMPNormMonth,
    prodMPProdDate
FROM    dbo.prodMPRunningTotals
WHERE        (prodMPNormMonth <= 6)

I need to select the First row that corresponds to the Max prodMPLiquid in those first 6 rows. There can be multiple rows with that same Max value so I have to pick just the first one.

How can I go about doing that efficiently for tens of thousands of prodMPEntity, which could each have dozens of individual lines?

prodMPEntity    prodMPLiquid    prodMPNormMonth prodMPProdDate
1300210052797290000 1102    1   3/31/2011
1300210052797290000 3790    2   4/30/2011
1300210052797290000 2656    3   5/31/2011
1300210052797290000 2125    4   6/30/2011
1300210052797290000 2758    5   7/31/2011
1300210052797290000 2403    6   8/31/2011
1300210052799490000 32      1   6/30/2010
1300210052799490000 373     2   7/31/2010
1300210052799490000 906     3   8/31/2010
1300210052799490000 468     4   9/30/2010
1300210052799490000 906     5   10/31/2010
1300210052799490000 170     6   11/30/2010
1300210052800090000 1380    1   9/30/2010
1300210052800090000 8285    2   10/31/2010
1300210052800090000 6201    3   11/30/2010
1300210052800090000 3759    4   12/31/2010
1300210052800090000 3407    5   1/31/2011
1300210052800090000 2023    6   2/28/2011

score 0 · Answer 1 · answered Oct 07 '16 at 08:02

This is a simple task for a ranking function:

WITH cte AS
 (
   SELECT  prodMPEntity, 
       prodMPLiquid,
       prodMPNormMonth,
       prodMPProdDate,
       ROW_NUMBER() -- assign a rank based on your description
       OVER (PARTITION BY prodMPEntity
             ORDER BY prodMPLiquid DESC, prodMPProdDate) AS rn
       FROM    dbo.prodMPRunningTotals
       WHERE        (prodMPNormMonth <= 6)
 )
SELECT   prodMPEntity, 
    prodMPLiquid,
    prodMPNormMonth,
    prodMPProdDate
FROM cte
WHERE rn = 1 -- return the row which is ranked 1st

Return Entire Row which has the first Max Value in One Field

1 Answers1