Post reply

How to get the maximum value per row?

  • October 15, 2016 at 12:19 pm

    #313973

    I have a table with a series of counts, and need to select the column with the max value only.

    here is my sample data:

    Create Table #Counter (CKey int, CA int, CB int,CC int,CD int,CE int)

    Insert Into #Counter

    Values(1, 2, 3, 2, 1, 4), (2, 3, 2, 3, 2, 4), (3, 2, 3, 5, 3, 2), (4, 3, 5, 2, 1, 5)

    Select*From#Counter

    The raw data:

    CKeyCACBCCCDCE

    123214

    232324

    323532

    435215

    How can I select the Maximum value only per row? My expected result would be:

    CKeyMaxCount

    1 4

    2 4

    3 5

    4 5

    Thank you all!

  • October 15, 2016 at 2:31 pm

    #1906761

    one way maybe...??

    CREATE TABLE #Counter

    (CKey INT,

    CA INT,

    CB INT,

    CC INT,

    CD INT,

    CE INT

    );

    INSERT INTO #Counter

    VALUES

    (1, 2, 3, 2, 1, 4),

    (2, 3, 2, 3, 2, 4),

    (3, 2, 3, 5, 3, 2),

    (4, 3, 5, 2, 1, 5)

    SELECT CKey,

    (

    SELECT MAX(colcnt)

    FROM(VALUES(CA), (CB), (CC), (CD), (CE)) x(colcnt)

    ) AS Maxcount

    FROM #Counter;

    DROP TABLE #Counter

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • October 15, 2016 at 2:35 pm

    #1906762

    This should do it...

    SELECT tbl.CKey, ca.VX

    FROM #Counter tbl

    CROSS APPLY (SELECT MAX(VX) FROM (VALUES (CA),(CB),(CC),(CD),(CE))v(VX))ca(VX)

    ;

    The FROM (VALUES) combination in the CROSS APPLY effectively unpivots each row of data and then find the MAX from those 5 items for each row. There is a faster way that compares the individual columns of the rows but the code is much longer for the bit of extra speed you might get.

    And, thanks for making the readily consumable test data! You made it real easy to answer your post with tested code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 15, 2016 at 2:36 pm

    #1906763

    Heh... crud. JLS is faster then I am. His post wasn't there when I started mine.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 15, 2016 at 2:37 pm

    #1906764

    This works perfectly!

    Thanks!

  • October 15, 2016 at 2:39 pm

    #1906765

    This works as well, and thank you for the explanations!

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

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