Max of multiple fields - SQL Server Forums

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

SQL Server Development (2000)

Max of multiple fields

Author

Topic

jtstruck
Starting Member

6 Posts

Posted - 2006-01-13 : 08:35:24

Sorry if this has been posted before, but I couldn't find anything for what I'm looking for. I need to find the max value between six different cost fields that I have. I have all the costs populated, now I just need one more field that will show the max of the six. Thanks for your help.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-13 : 08:47:55

U write a function to find Max value from 6 parameters
-- Use standard algorithm to find the max of given six figures

use that in the query with appropriate field names
-- eg. Select MaxFunction(Field1, Field2, ..... Field6) from Mytbl

jtstruck
Starting Member

6 Posts

Posted - 2006-01-13 : 09:05:14

Could I get a little help on what the syntax of the function might look like? I hate to be that guy, but I'm not sure where to start with it...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-13 : 09:17:15

Only a little, heh heh

-- My logic may not be efficient

Have a variable as @MaxVal
Check each 2 of the six parameters passed to the function, as sets of 2. Then of each set if one is greater than the other, assign that with value to the @MaxVal variable. Do it till the end of all 6 variables

ie. First get Parameters 1 & 2 Out of those check which one is higher
assign that to th @MaxVal, then compare that with parameter 3, do the same as above.
Repeat for Parameters 4, 5, 6
Finally output the @MaxVal

or u can fill all these data to a temp table as rows and using the Max function u can get the max value

U can use efficient algorithms to do that, but u may not need complex logic to find the max of 6 values

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 09:22:24

or

Select max(col1,col2,...,col6) from
(
---your query
) T

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-13 : 09:37:33

>> max(col1,col2,...,col6)
I don't this will work. max() only accept 1 input.

Do something like this. I only implement 3 values

create function dbo.maxvalue(@col1 as int, @col2 as int, @col3 as int) 
returns int
as
begin
	return (select max(value) from (select @col1 as value union all select @col2 union all select @col3) as v)
end

-----------------
'KH'
if you can't beat them, have someone else to beat them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources