Max Value

[ofsouto]

Hi, dear
Is there any way to select the max value between 2 or more collumns? Datatype - numeric.
Best Regards
Obede

 

[zhavic]

There may be better solution, but for example:

CREATE FUNCTION min_value
(
@Column1 numeric(10,2),
@Column2 numeric(10,2),
@Column3 numeric(10,2),
@Column4 numeric(10,2)
)
RETURNS numeric(10,2)
BEGIN
DECLARE @work_table TABLE ( number numeric(10,2) )

INSERT INTO @work_table VALUES( @Column1 )
INSERT INTO @work_table VALUES( @Column2 )
INSERT INTO @work_table VALUES( @Column3 )
INSERT INTO @work_table VALUES( @Column4 )

RETURN ( SELECT MIN( number ) FROM @work_table )
END

-------------------------
And than try

SELECT dbo.min_value( Some_column, Some_other_column, Col1, Col2 )
FROM source_table

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[zhavic]

Sorry, just replace MIN with MAX

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[dmhirsch]

How about:
if max(col1) > max(col2) then max(col1) else max(col2) end

 

[essa2000]

Dear ;
I wrote this code to find out maximum b/w two columns and I can write it for three but more columns it will be complex so , if you want to use it for two then you can use it and check it for your values.

Select 'MaxValue' = Case
When Max1 >= Max2 then Max1
else Max2
end
from (Select Max(value1) as Max1 , Max(value2) as Max2 from MaxV ) Table1

Notes: The table which has data is MaxV and it has two columns and the table named "Table1" is kind of derived table and which is created on run on time and it has only maximum values of both the columns and then you can use Case statement to find out the maximum value b/w those two maximum values. I did the same in above code ! You can change it for three column.

It is my method , which I just thought to solve your problem. if anyone has dynamic method then please post that code !

Thanks.


Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[zhavic]

Now I read this posts and I am little confused, because
'select the max value between 2 or more collumns'

has two senses:

1. select max value between 2 or more collumns for each row ( that is my post )

2. select max value from max values of columns ( that is your posts )

And this second way may be done with function that I post too:


SELECT dbo.max_value( MAX( Some_column ), MAX( Some_other_column ), MAX( Col1 ), MAX( Col2 ) )
FROM source_table

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[essa2000]

I request ofsouto to come in and clear what he wants ! Now, I am also confused ! Either he got his answer !

Thanks.

Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[ofsouto]

I want to know the max value between 2 or more collumns.

register: collum_1 = 10, collum_2 = 20, collum_3 = 30

I want to get the collum_3 value (30)

Is it clear?

Thanks

 

[zhavic]

Little modification of my function for use with 1 to 6 columns:

CREATE FUNCTION max_value
(
@Column1 numeric(10,2) = 0,
@Column2 numeric(10,2) = 0,
@Column3 numeric(10,2) = 0,
@Column4 numeric(10,2) = 0,
@Column5 numeric(10,2) = 0,
@Column6 numeric(10,2) = 0
)
RETURNS numeric(10,2)
BEGIN
DECLARE @work_table TABLE ( number numeric(10,2) )

INSERT INTO @work_table VALUES( @Column1 )
INSERT INTO @work_table VALUES( @Column2 )
INSERT INTO @work_table VALUES( @Column3 )
INSERT INTO @work_table VALUES( @Column4 )
INSERT INTO @work_table VALUES( @Column5 )
INSERT INTO @work_table VALUES( @Column6 )

RETURN ( SELECT MAX( number ) FROM @work_table )
END

-------------------------------
Using:
/* for 2 columns */
SELECT dbo.max_value( Column1, Column2 ) FROM ...
/* for 3 columns */
SELECT dbo.max_value( Column1, Column2, Column3 ) FROM ...
/* for 4 columns */
SELECT dbo.max_value( Column1, Column2, Column3, Column4 ) FROM ...

and so on ...

------------------------------------
But, if use don't want or can't use function, you must do this with CASE ... END statements:


/* for 3 columns */
SELECT
CASE WHEN column1 > column2 AND column1 > column3 THEN column1
WHEN column2 > column3 THEN column2
ELSE column3
END

/* for 4 columns */
SELECT
CASE WHEN column1 > column2 AND column1 > column3 AND column1 > column4 THEN column1
WHEN column2 > column3 AND column2 > column4 THEN column2
WHEN column3 > column4 THEN column3
ELSE column4
END

/* for 5 columns */
SELECT
CASE WHEN column1 > column2 AND column1 > column3 AND column1 > column4 AND column1 > column5 THEN column1
WHEN column2 > column3 AND column2 > column4 AND column2 > column5 THEN column2
WHEN column3 > column4 AND column3 > column5 THEN column3
WHEN column4 > column5 THEN column4
ELSE column5
END

and so on ...


Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.