Best Practice for returning the highest value in a set of columns?

Question

SQL Isn't my Forte, Some help from you experts would be amazing :)

Dataset:

 Name of Product  |  Part Number   |   Size 1  |  Size 2 |  Size 3
------------------|----------------|-----------|---------|----------
      Item1       |    12345       |     4     |    4    |     6
      Item2       |    54321       |     4     |    5    |     4
      Item3       |    54123       |     6     |    2    |     2

I need to return the highest value in the 3 sizes and aggregate them into a single column.

Item1  |  6
Item2  |  5
Item3  |  6

Googling has only lead me to the MAX() function, but it just returns the highest value in the dataset which is not what im after.

Yup, I didnt find that post, sorry for the duplicate. @AHiggens your answer worked for me, thanks! — John Doe, Apr 23 '15 at 17:27
Spoke too soon, getting a '% has more columns than were specified in the column list.' on the value part of value(val) — John Doe, Apr 23 '15 at 17:52

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

Depending on your version of SQL Server (2008 or above), you could use a variation on the following:

SELECT 
    [Name Of Product],
    (SELECT MAX(Val)    FROM (VALUES ([Size 1]), ([Size 2]), ([Size 3])) AS value(val)) AS MaxSize
FROM MyTable

EDIT: Found the original source of this code, will mark your question as a potential duplicate based on that.

edited May 23 '17 at 10:24

Community

1
1

answered Apr 23 '15 at 17:13

AHiggins

7,029
6
36
54

StuartLC · Accepted Answer · 2015-04-24T06:09:35.043

2

You can use an UNPIVOT to split out the columns, and then the Maximum becomes a simple MAX() with GROUP BY against the relevant [Name of Product]

SELECT [Name of Product], Max(TheSize)
FROM Table1
UNPIVOT
(
    [TheSize]
    FOR Size in ([Size 1], [Size 2], [Size 3])
) x
GROUP BY [Name of Product];

SqlFiddle is back online - example here

edited Apr 24 '15 at 06:09

answered Apr 23 '15 at 17:19

StuartLC

104,537
17
209
285

This got it working, just had to add the rest of the fields in the table to the group by, thanks! – John Doe Apr 23 '15 at 18:23
Aha - hopefully you've solved the problem? Unfortunately SqlFiddle isn't working with SqlServer at the moment, but I do have a working sample. – StuartLC Apr 23 '15 at 18:29

score 0 · Answer 3 · answered Apr 23 '15 at 17:16

0

You can use a nested CASE statement. Someting like this:

SELECT [name of product], [part number], CASE WHEN size1 > size2 AND size1 > size 3 Then size1 ELSE CASE WHEN size2 > size 3 THEN size 2 ELSE size 3 END END
FROM ...

answered Apr 23 '15 at 17:16

Zohar Peled

79,642
10
69
121

Best Practice for returning the highest value in a set of columns?

3 Answers3