Post reply

Select a set of Maximum (or minimum) values with a associated data

  • April 25, 2012 at 4:33 am

    #259887

    I have 3 fields. ProductCode,Date, and Value

    so for every product code I have multiple dates with values

    I need to select the ProductCode and say the Maximum value

    So I did something like

    Select ProductCode, Max(Value) from TestTable group by ProductCode

    But I need the date on which it was Maximum

    Can someone suggest a method

  • April 25, 2012 at 4:56 am

    #1478846

    Hi

    Its very difficult to help without table structures and test data however you could try something like the following:

    SELECT *

    FROM

    (

    ProductCode

    ,Date

    ,Value

    ,ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY Value DESC) AS RowNum

    FROM

    Product

    ) AS Prod

    WHERE

    Prod.RowNum = 1

    And if you want the MIN

    SELECT *

    FROM

    (

    ProductCode

    ,Date

    ,Value

    ,ROW_nUMBER() OVER (PARTITION BY ProductCode ORDER BY Value ASC) AS RowNum

    FROM

    Product

    ) AS Prod

    WHERE

    Prod.RowNum = 1

    Andy

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • April 25, 2012 at 6:31 am

    #1478909

    +1 Andy...you beat me to it 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • April 25, 2012 at 6:49 am

    #1478921

    Hi Andy

    Thanks for that. I got it working with your pointer

    I still need to read up on this partion thing

  • April 25, 2012 at 6:57 am

    #1478929

    Your welcome 🙂

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply