How to find out the maximum among the four columns

Question

Post reply

How to find out the maximum among the four columns

Ezhilan

Ten Centuries

Points: 1319
More actions
July 25, 2007 at 4:38 am

#175430

How to find out the maximum among the four columns and update it in the fifth column
For Example
create table dbo.test
(
max1numeric,
max2 numeric,
max3numeric,
max4 numeric,
max5numeric
)
insert into test VALUES
(4,6,7,9,NULL)
-----
Here,
The column max5 should be updated with the value of column max4 (that is '9').
How can I go about it?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 1

There is no built in functionality within SQL Server to achieve this. You will have to build something yourself.

For example, you could write a function where you pass in the 4 values and it returns the largest value:

create function dbo.fn_max_value (@val1 numeric, @val2 numeric, @val3 numeric, @val4 numeric)
returns numeric
as
begin
declare @table table
(val numeric)
insert @table select @val1
insert @table select @val2
insert @table select @val3
insert @table select @val4
return (select max(val) from @table)
end

Your insert statement then becomes:

insert into test VALUES
(4,6,7,9,dbo.fn_max_value(4,6,7,9))

J

Vladan SSC-Insane Points: 22072 More actions · Answer 2

I suppose you have more than one row in the table... so you should have some key to know which row is which. If there isn't a natural primary key, create identity column:

ALTER TABLE test ADD test_id INT IDENTITY

Then you can for example do this to update all rows at once:

UPDATE t

SET max5 = X.totalmax

FROM test t

JOIN

(SELECT Q.test_id, MAX(Q.maxval) as totalmax

FROM

(SELECT test_id, max1 as maxval FROM test

UNION

SELECT test_id, max2 as maxval FROM test

UNION

SELECT test_id, max3 as maxval FROM test

UNION

SELECT test_id, max4 as maxval FROM test) as Q

GROUP BY Q.test_id) as X ON X.test_id = t.test_id

One never knows until it is tested, but this should be considerably quicker on larger datasets than a function, because UDF forces row by row execution, while this runs as a set-based statement.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You'd be surprised on this one... you're version forces 5 table scans while the udf takes only one and some overhead to call the function.

The fastest version of this is to use a case statement to do the update... single table scan, no udf call .

max5 = CASE WHEN Max1 > Max2 AND Max1 > Max3 AND Max1> Max4 THEN MAX1

ELSE CASE WHEN Max2 .....

END END END...

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Thanks Remi... I wasn't really happy with the idea of multiple UNION, but didn't think of anything better at the moment. CASE was my second idea, but it just passed through my head without taking some real shape probably I was lazy to bother with creating all the comparisons. Good to know that, I will try to remember it.

I never needed anything like that in practice so far, to be honest. Looks like creating a report in two steps... first fill max1-max4 and then find what is the maximum of the maximums?

Ezhilan Ten Centuries Points: 1319 More actions · Answer 5

Thanks...to everyone..

We have finally implemented the CASE logic and it is working fine.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 6

use > or >=???, just in case there are two or more values that are maximum. Also, what if one of the compared to values is NULL?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

I think this is a better solution.

It will be faster than a UDF.

It is easier to scale up to as many columns as you need than using a CASE statement, because you only have to add a single line to the UNION ALL. Also, it does not have the bug that people usually leave when trying to do it with a CASE where they fail to account for columns with equal values. It also handles cases where one or more of the columns is null.

It’s also easier to code and test.

select 
 [Max_of_MAX1_to_MAX4] =     
  (
  select
   X1= max(bb.xx)
  from
   (
   select xx = a.max1 where a.max1 is not null union all
   select xx = a.max2 where a.max2 is not null union all
   select xx = a.max3 where a.max3 is not null union all
   select xx = a.max4 where a.max4 is not null
   ) bb
  ),
 a.max1,
 a.max2,
 a.max3,
 a.max4
from
 dbo.test a

The code below is functionally equivalent using a CASE statement. I'll let you decide which is easier to code and debug, especially if you need to extend it to say 10 columns.

 select
   [Max_of_MAX1_to_MAX4] =  
 case
 when  a.Max1 is not null   and
  (a.Max1 >= a.Max2 or a.Max2 is null) and
  (a.Max1 >= a.Max3 or a.Max3 is null) and
  (a.Max1 >= a.Max4 or a.Max4 is null)
 then a.Max1
 when  a.Max2 is not null   and
  (a.Max2 >= a.Max1 or a.Max1 is null) and
  (a.Max2 >= a.Max3 or a.Max3 is null) and
  (a.Max2 >= a.Max4 or a.Max4 is null)
 then a.Max2
 when  a.Max3 is not null   and
  (a.Max3 >= a.Max1 or a.Max1 is null) and
  (a.Max3 >= a.Max2 or a.Max2 is null) and
  (a.Max3 >= a.Max4 or a.Max4 is null)
 then a.Max3
 when  a.Max4 is not null   and
  (a.Max4 >= a.Max1 or a.Max1 is null) and
  (a.Max4 >= a.Max2 or a.Max2 is null) and
  (a.Max4 >= a.Max3 or a.Max3 is null)
 then a.Max4
 else null
 end,
 a.Max1,
 a.Max2,
 a.Max3,
 a.Max4
from
 dbo.test a

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

If anyone is interested, I ran performance tests of the 2 methods from my prior post, and posted the results on the link below.

MIN/MAX Across Multiple Columns

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Did you try to compare it to performance of a query on properly designed tables?

_____________
Code for TallyGenerator

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

If the tables were properly designed, there would be no need for this thread.