How can I select the min value across several columns for a record

  • Hello,

    consider:

    ;with mycols( ID, colvalue1, colvalue2, colvalue3) AS (

    SELECT 1, 3, 1, 2

    UNION SELECT 2, 5, 7, 4

    UNION SELECT 3, 37, 9, 22

    )

    SELECT * FROM myCols

    I need build a way to return to least value for the value columns for a particular record,

    ID minvalue

    1 1

    2 4

    3 9

    Does anyone have a technique to do this? (I need a 'fast' way: not millions, but thousands of records...)

    Thanks in advance,

    Mark

    Mark
    Just a cog in the wheel.

  • Try using case like this:

    ;

    with mycols(ID, colvalue1, colvalue2, colvalue3)

    AS (SELECT

    1,

    3,

    1,

    2

    UNION

    SELECT

    2,

    5,

    7,

    4

    UNION

    SELECT

    3,

    37,

    9,

    22)

    SELECT

    ID,

    CASE WHEN colvalue1 <= colvalue2 AND

    colvalue1 <= colvalue3 then colvalue1

    WHEN colvalue2 <= colvalue3 THEN colvalue2

    ELSE colvalue3

    END,

    colvalue1,

    colvalue2,

    colvalue3

    FROM

    myCols

  • Look up the PIVOT operator in Books Online, I think that should do the trick for you.



    A.J.
    DBA with an attitude

  • A.J. Wilbur (5/22/2009)


    Look up the PIVOT operator in Books Online, I think that should do the trick for you.

    Thanks A.J. that did it...

    Jack's solution would have worked, but I didn't fully describe that I acually had 17 columns to survey...

    here's my test code that works (I'll build a function to do the real dirty work:

    ;with mycols( ID, colvalue1, colvalue2, colvalue3) AS (

    SELECT 1, 3, 1, 2

    UNION SELECT 2, 5, 7, 4

    UNION SELECT 3, 37, 9, 22

    )

    SELECT ID, [theminimumvalue] = MIN(mymin)

    FROM

    (SELECT ID, colvalue1, colvalue2, colvalue3

    FROM myCols) AS p

    UNPIVOT

    (myMin FOR zCol IN

    (colvalue1, colvalue2, colvalue3)

    )AS unpvt

    GROUP BY ID

    GO

    returns:

    IDtheminimumvalue

    11

    24

    39

    seems to do it..

    Cheers and best regards for Memorial Day,

    mark

    Mark
    Just a cog in the wheel.

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

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