MAX() FROM VALUES with GROUP BY

  • Hi all,

    I know you can use the MAX(v) FROM (VALUES () AS VALUE(v)) query to select the max value from a series of columns. How can this be applied with a GROUP BY to select the max of those max values?

    For example,

    CREATE TABLE #Temp
    (
     ID INT IDENTITY PRIMARY KEY,
     GroupVal INT,
     Val2 INT,
     Val3 INT,
     Val4 INT
    )

    INSERT INTO #Temp (GroupVal, Val2, Val3, Val4) VALUES (1, 2, 3, 4), (1, 3, 4, 7), (1, 4, 2, 1), (2, 1, 3, 5), (2, 4, 1, 3), (3, 1, 2, 3)
    SELECT
     GroupVal,
     (
      SELECT MAX(v)
      FROM (VALUES (Val2), (Val3), (Val4)) AS VALUE(v)
     )
    FROM #Temp
    DROP TABLE #Temp

    That gives me the max value from each row, of the three columns val1, val2, and val3. But, I can't surround that with a MAX and GROUP BY GroupVal, since I'm aggregating an aggregate.

    I know I can do something like this:

    CREATE TABLE #Temp
    (
     ID INT IDENTITY PRIMARY KEY,
     GroupVal INT,
     Val2 INT,
     Val3 INT,
     Val4 INT
    )

    INSERT INTO #Temp (GroupVal, Val2, Val3, Val4) VALUES (1, 2, 3, 4), (1, 3, 4, 7), (1, 4, 2, 1), (2, 1, 3, 5), (2, 4, 1, 3), (3, 1, 2, 3)
    SELECT
     GroupVal,
     MAX(CASE WHEN Val4 > Val3 AND Val4 > Val2 THEN Val4 WHEN Val3 > Val2 AND Val3 > Val4 THEN Val3 ELSE Val2 END)
    FROM #Temp
    GROUP BY GroupVal
    DROP TABLE #Temp

    But that's hideously ugly, and gets incredibly complex when the number of columns increases.

    Any suggestions?

  • Hm. Can't find any way to delete the question, so I guess I'll just answer it with the obvious solution that I didn't think of before writing the question πŸ˜›

    CREATE TABLE #Temp
    (
     ID INT IDENTITY PRIMARY KEY,
     GroupVal INT,
     Val2 INT,
     Val3 INT,
     Val4 INT
    )

    INSERT INTO #Temp (GroupVal, Val2, Val3, Val4) VALUES (1, 2, 3, 4), (1, 3, 4, 7), (1, 4, 2, 1), (2, 1, 3, 5), (2, 4, 1, 3), (3, 1, 2, 3)
    SELECT
     GroupVal,
     MAX(MaxVal)
    FROM
    (
     SELECT
      GroupVal,
      (
       SELECT MAX(v)
       FROM (VALUES (Val2), (Val3), (Val4)) AS VALUE(v)
      ) AS MaxVal
     FROM #Temp
    ) r
    GROUP BY GroupVal
    DROP TABLE #Temp
  • Have you ever used UNION or UNION ALL ?    Try the following, which isn't all that bad in terms of how you'll have to expand it as the number of columns grows.   Eventually, you might want to go to dynamic SQL for it, but that's not really necessary.
    CREATE TABLE #Temp (
        ID INT IDENTITY PRIMARY KEY,
        GroupVal INT,
        Val2 INT,
        Val3 INT,
        Val4 INT
    );
    INSERT INTO #Temp (GroupVal, Val2, Val3, Val4)
        VALUES    (1, 2, 3, 4),
                (1, 3, 4, 7),
                (1, 4, 2, 1),
                (2, 1, 3, 5),
                (2, 4, 1, 3),
                (3, 1, 2, 3);

    SELECT X.GroupVal, MAX(X.Num) AS MaxValue
    FROM (
        SELECT GroupVal, Val2 AS Num
        FROM #Temp
        UNION ALL
        SELECT GroupVal, Val3 AS Num
        FROM #Temp
        UNION ALL
        SELECT GroupVal, Val4 AS Num
        FROM #Temp
        ) AS X
    GROUP BY X.GroupVal
    ORDER BY X.GroupVal;

    DROP TABLE #Temp;

  • kramaswamy - Thursday, August 24, 2017 10:25 AM

    Hm. Can't find any way to delete the question, so I guess I'll just answer it with the obvious solution that I didn't think of before writing the question πŸ˜›

    CREATE TABLE #Temp
    (
     ID INT IDENTITY PRIMARY KEY,
     GroupVal INT,
     Val2 INT,
     Val3 INT,
     Val4 INT
    )

    INSERT INTO #Temp (GroupVal, Val2, Val3, Val4) VALUES (1, 2, 3, 4), (1, 3, 4, 7), (1, 4, 2, 1), (2, 1, 3, 5), (2, 4, 1, 3), (3, 1, 2, 3)
    SELECT
     GroupVal,
     MAX(MaxVal)
    FROM
    (
     SELECT
      GroupVal,
      (
       SELECT MAX(v)
       FROM (VALUES (Val2), (Val3), (Val4)) AS VALUE(v)
      ) AS MaxVal
     FROM #Temp
    ) r
    GROUP BY GroupVal
    DROP TABLE #Temp

    -- Simpler and more intuitive using APPLY

    SELECT

    GroupVal,

    MAX(x.MaxVal)

    FROM #Temp

    CROSS APPLY (SELECT MaxVal = MAX(v) FROM (VALUES (Val2), (Val3), (Val4)) AS VALUE(v)) x

    GROUP BY GroupVal

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • CTE and cross apply looks good option

    with CTE as
    (
    SELECT
    GroupVal,
    (
    SELECT MAX(v)
    FROM (VALUES (Val2), (Val3), (Val4)) AS VALUE(v)
    ) val
    FROM #Temp
    )
    select GroupVal, max(val) from cte
    group by GroupVal

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

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