SQL - Select the largest value within a row

Question

I seem to be stuck on this and can't find a solution having had a look around.

I have an SQL table who's first row looks something like this:

Name   Val1   Val2   Val3
John   1000   2000   3000

What I need to do is Select the largest value within this row i.e. 3000

Obviously if these values were in a column rather than row you could just use SELECT MAX(column) FROM table to get the largest value in the column. Is there an equivalent of this for finding the max value in a row?

I have also had a look at the uses of PIVOT and UNPIVOT but I don't think they are useful to me here..

The only way I have been able to do it is to create a temp table and insert each value into a single column like so:

CREATE TABLE #temp (colvals float)
     INSERT INTO #temp (colvals)
          SELECT Val1 FROM table WHERE ID=1
         UNION
          SELECT Val2 FROM table WHERE ID=1
         UNION
          SELECT Val3 FROM table WHERE ID=1
--------------------------------------------
SELECT MAX(colvals) FROM #temp
--------------------------------------------
DROP TABLE #temp

However I feel this is rather slow especially as my table has a lot more columns than the snippet I have shown above.

Any ideas?

Thanks in advance.

UNION ALL is probably faster, but maybe not the best solution. — jarlh, Dec 02 '15 at 09:14
If you really have 20 columns then you can search for a built in function. That failing, maybe you can create a custom function. — Tim Biegeleisen, Dec 02 '15 at 09:22

Eric · Accepted Answer · 2015-12-02T09:28:36.200

You can build a reference table for columns by APPLY and use native MAX()

-- Sample Data
declare @data table (Name varchar(10), Val1 int, Val2 int, Val3 int, Val4 int, Val5 int, Val6 int)
insert @data values 
    ('John', 1000, 2000, 3000, 4000, 5000, 6000),
    ('Mary', 1, 2, 3, 4, 5, 6)


select Name, MaxValue from 
    @data 
    cross apply 
    (
        select max(value) as MaxValue 
        from 
            (values
                (Val1),(Val2),(Val3),(Val4),(Val5),(Val6) -- Append here
            ) t(value)
    ) result

SQL Fiddle

score 2 · Answer 2 · answered Dec 02 '15 at 09:16

2

select MAX(case when c1 > c2 and c1 > c3 then c1
                when c2 > c3 then c2
                else c3
           end)
from tablename

answered Dec 02 '15 at 09:16

jarlh

42,561
8
45
63

This would certainly work however my table has over 20 columns so this query would get very long and messy and probably not very efficient.? – Johnathan Dec 02 '15 at 09:21
@Johnathan, with that many columns I'd go with another solution. – jarlh Dec 02 '15 at 09:38

Thanos Markou · Answer 3 · 2015-12-02T09:26:44.633

You need something like this:

SELECT *, Row_Number() OVER (ORDER BY GETDATE()) Rowid INTO #temp From yourtable

DECLARE @Columns AS Varchar(MAX)
SET @Columns =''
SELECT @Columns = @Columns + ',[' + name + ']' FROM tempdb..syscolumns 
WHERE id=object_id('tempdb..#temp') AND name <> 'Rowid'

SELECT @Columns = Right(@Columns, len(@Columns)-1)
exec ('Select Rowid,Max(val) maxval from #temp t Unpivot(val For data in (' + @Columns + ')) as Upvt Group by Rid')

Drop table #temp

score 1 · Answer 4 · answered Dec 02 '15 at 09:26

1

Use math logic:

select 
  case
    when val1 >= val2 and val1 >= val2 then val1
    when val2 >= val1 and val2 >= val3 then val2
    else val3
  end maxVal
from mytable
where id = 1

answered Dec 02 '15 at 09:26

Bohemian

412,405
93
575
722

score 1 · Answer 5 · answered Dec 02 '15 at 09:35

I think you were on the right track when you looked at unpivot as an option. Becaue that's exactly what you want to do - you have a pivot table, and you want the unpivoted value from it. Here's what I came up with:

declare @base table (Name char(4),   Val1   int, Val2   int ,Val3 int);
insert into @base (Name,   Val1 ,  Val2 ,  Val3) values ('John' ,  1000  , 2000 ,  3000);

select name, max(value) as max_value 
from (
    select name, valuetype, value
    from  @base b
    unpivot ( value for valuetype in (Val1 ,  Val2 ,  Val3)) as u 
     ) as up
group by name

To expand to your whole table, you can then just add more column names to the unpivot row:

unpivot ( value for valuetype in (Val1 ,  Val2 ,  Val3, ... more values here...)) as u

score 0 · Answer 6 · edited May 23 '17 at 12:23

You could always replicate this answer Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

-- Sample Data
declare @data table (Name varchar(10), Val1 int, Val2 int, Val3 int, Val4 int, Val5 int, Val6 int)
insert @data values 
    ('John', 1000, 2000, 3000, 4000, 5000, 6000),
    ('Mary', 1, 2, 3, 4, 5, 6),
    ('Tony66', 1, 2, 3, 4, 5, 66),
    ('Tony55', 1, 2, 3, 4, 55, 6),
    ('Tony44', 1, 2, 3, 44, 5, 6),
    ('Tony33', 1, 2, 33, 4, 5, 6),
    ('Tony22', 1, 22, 3, 4, 5, 6),
    ('Tony11', 11, 2, 3, 4, 5, 6)

SELECT name,
       (SELECT MAX(value)
        FROM (VALUES (Val1),(Val2), (Val3), (Val4), (Val5), (Val6)) AS AllValues(value)) AS 'MaxValue'
FROM @data

SQL - Select the largest value within a row

6 Answers6