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.

Answer 1

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