MAX() FROM VALUES with GROUP BY

Question

MAX() FROM VALUES with GROUP BY

kramaswamy

SSCoach

Points: 18181
More actions
August 24, 2017 at 10:18 am

#338816

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?

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

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

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

sgmunson SSC Guru Points: 110640 More actions · Answer 2

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;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Avi1 SSCrazy Points: 2323 More actions · Answer 4

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