CODEWITHSUNDEEP
sqlmaxssms
Johnathan
Johnathan

Reputation: 909

SQL - Select the largest value within a row

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.

Upvotes: 6

Views: 13828

Answers (6)

jarlh
jarlh

Reputation: 44766

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

Edit: Modern SQL Server versions have the GREATEST() function:

select GREATEST(c1, c2, c3)
from tablename

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql

Upvotes: 3

Kane
Kane

Reputation: 16802

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

Upvotes: 0

BeanFrog
BeanFrog

Reputation: 2315

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

Upvotes: 1

Eric
Eric

Reputation: 5733

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

Upvotes: 3

Bohemian
Bohemian

Reputation: 424993

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

Upvotes: 1

Thanos Markou
Thanos Markou

Reputation: 2623

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

Upvotes: 1

Related Questions