# MAX of two values

• July 21st, 07:01 PM
Leythos
MAX of two values
I'm converting an old power builder app over to stored
procs and would like to convert the MAX fuunction to
something I can use in MS SQL 2000.

I've tried stupid things like:

select (myvar > 0)*myvar
select max(1,2,3,4)
etc...

Any simple ideas that don't involve 'IF'
• July 21st, 07:08 PM
Tom
Re: MAX of two values
You can use a UDF:

create function dbo.MyMax
(
a int
, b int
)
returns int
as
begin
return (select case when a > b then a else b end)
end
go

select dbo.MyMax (1, 2)
select dbo.MyMax (1, dbo.MyMax (2, 3))

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
www.pinnaclepublishing.com/sql

"Leythos" <com> wrote in message news:031c01c34fba\$6f38f160\$gbl...
I'm converting an old power builder app over to stored
procs and would like to convert the MAX fuunction to
something I can use in MS SQL 2000.

I've tried stupid things like:

select (myvar > 0)*myvar
select max(1,2,3,4)
etc...

Any simple ideas that don't involve 'IF'

• July 21st, 07:14 PM
David
Re: MAX of two values
Here's an example:

CREATE TABLE SomeValues (X INTEGER PRIMARY KEY, Y INTEGER NULL, Z INTEGER
NULL)

INSERT INTO SomeValues VALUES (1,2,3)
INSERT INTO SomeValues VALUES (2,1,3)
INSERT INTO SomeValues VALUES (3,2,NULL)

SELECT *,
(SELECT MIN(v)
FROM (SELECT X AS v UNION ALL SELECT Y UNION ALL SELECT Z) m
HAVING COUNT(v)=COUNT(*) -- Result only if all values are non-NULL
)
AS minimum,
(SELECT MAX(v)
FROM (SELECT X AS v UNION ALL SELECT Y UNION ALL SELECT Z) m
HAVING COUNT(v)=COUNT(*) -- Result only if all values are non-NULL
)
AS maximum
FROM SomeValues

If you want to ignore NULLs then drop the HAVING clause.

--
David Portas
------------
--

• July 21st, 07:15 PM
Russell
Re: MAX of two values
You can use the CASE statement in T-SQL to create a user-defined function
like:

CREATE FUNCTION (AParm INT, BParm INT)
RETURNS INT
AS
DECLARE ReturnVal INT
CASE
WHEN AParm >= BParm THEN
SET ReturnVal = AParm
ELSE
SET ReturnVal = BParm
END
RETURN (ReturnVal)

Russell Fields

"Leythos" <com> wrote in message
news:031c01c34fba\$6f38f160\$gbl...

• July 21st, 07:15 PM
Aaron
Re: MAX of two values
There is no built-in method for doing this.

You could do this:

CREATE TABLE ints(i INT)
INSERT ints VALUES(1)
INSERT ints VALUES(2)
INSERT ints VALUES(3)
INSERT ints VALUES(4)
SELECT MAX(i) FROM ints

Or you could do this:

SELECT Max(nbr) FROM (SELECT nbr = 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4) n

Or you could use a comma-separated list to generate a table, e.g. see
http://www.aspfaq.com/2248

"Leythos" <com> wrote in message
news:031c01c34fba\$6f38f160\$gbl...

• July 21st, 07:33 PM
Leythos
MAX of two values
Thanks everyone.

I like the function and the methods using case. While I
was already aware of those I was hoping that someone had a
little trick in their back pocket.

Thanks again,
Leythos

• July 21st, 07:37 PM
Aaron
Re: MAX of two values
Are you always going to have 4 values? If not, it will be tricky to nest
function calls and/or CASE ...

This is why I suggested the table method... if you have a comma-separated
list, it would be trivial to jam those into a table and use SELECT MAX() the
way it was meant to be used...

A

"Leythos" <com> wrote in message
news:016b01c34fbe\$fdca25d0\$gbl...