Calculating Min/Max against Three Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating Min/Max against Three Columns

Author

Topic

Lopaka
Starting Member

48 Posts

Posted - 2007-11-06 : 13:58:42

I am trying to get the Min and/or Max of Three Columns in a record. Is there an easy way to do this?
Example

fld1, fld2, fld3
10, 15, 20 <= Min=10, Max(20)

Thank you

Robert R. Barnes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 14:11:07

It depends. Do you also have a identity column on the table?

E 12°55'05.25"
N 56°04'39.16"

Lopaka
Starting Member

48 Posts

Posted - 2007-11-06 : 14:33:37

There is no id column... I do not see why you would need one to find the min/max of a recordset? I am currently using a (case) function to determine the min/max. was hoping there was an easier way.

select minfld = (case when fld1<fld2 and fld1<fld3 then fld1
when fld2<fld1 and fld2<fld3 then fld2
when fld3<fld1 and fld3<fld2 then fld3 end),
maxfld = = (case when fld1>fld2 and fld1>fld3 then fld1
when fld2>fld1 and fld2>fld3 then fld2
when fld3>fld1 and fld3>fld2 then fld3 end)

Robert R. Barnes

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-06 : 17:43:47

Take a look at this topic:

MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

CODO ERGO SUM

Reporter
Starting Member

48 Posts

Posted - 2007-11-07 : 05:23:08

declare @t table (f1 int,f2 int,f3 int)

insert @t
select 1,2,3 union all
select 4,2,3 union all
select 1,6,3 union all
select 7,2,7 union all
select 9,4,3

select
minval=case when f1<=f2 and f1<=f3 then f1 else case when f2<=f3 then f2 else f3 end end,
maxval=case when f1>=f2 and f1>=f3 then f1 else case when f2>=f3 then f2 else f3 end end
from @t

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 06:02:42

That is a nice summary of the link MVj provided.

E 12°55'05.25"
N 56°04'39.16"

Reporter
Starting Member

48 Posts

Posted - 2007-11-08 : 04:11:48

haha :)

How about something like...

declare @t table (f1 float,f2 float,f3 float)
insert @t
select 1,2,8 union all
select 4,3,2 union all
select 3,9,5 union all
select 7,6,0 union all
select 1,5,7

select
minval=0.25*((f1+f2+2*f3-abs(f1-f2))-abs(f1+f2-2*f3-abs(f1-f2))),
maxval=0.25*((f1+f2+2*f3+abs(f1-f2))+abs(f1+f2-2*f3+abs(f1-f2))),
from @t

How you can see this select does not contains any case statment.
And It's possible to do the same for not only three fields...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 04:35:54

Nice trick but much slower.
Here is data from Profiler

Records	CPU	READS	method
405769	2156	1661	formula
405769	 532	1661	case

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-08 : 09:50:48

No one ever shows how to deal with the possibility of one or more of the columns being null, but in this type of table design it almost a certain that some of the columns will be nullable.

I think it will be very hard to implement that formula from Reporter with nullable columns.


select
	[Min_of_fld1_to_fld3_Method_1] =     
  	(
  	select
   		X1= min(bb.xx)
  	from
   		(
   		select xx = a.fld1 where a.fld1 is not null union all
   		select xx = a.fld2 where a.fld2 is not null union all
   		select xx = a.fld3 where a.fld3 is not null
   		) bb
	) ,

   	[Min_of_fld1_to_fld3_Method_2] =  
 	case
 	when 	a.fld1 is not null   and
  		(a.fld1 <= a.fld2 or a.fld2 is null) and
  		(a.fld1 <= a.fld3 or a.fld3 is null)
 	then 	a.fld1
 	when  	a.fld2 is not null   and
  		(a.fld2 <= a.fld1 or a.fld1 is null) and
  		(a.fld2 <= a.fld3 or a.fld3 is null)
 	then 	a.fld2
 	when  	a.fld3 is not null   and
  		(a.fld3 <= a.fld1 or a.fld1 is null) and
  		(a.fld3 <= a.fld2 or a.fld2 is null)
 	then 	a.fld3
 	else null
 	end
from
 	MyTable a

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources