MAX of two values
Author Message
MAX of two values

You can use the CASE statement in T-SQL to create a user-defined function
like:

RETURNS INT
AS

CASE

ELSE

END

Russell Fields

Quote:
> 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 max(1,2,3,4)
> etc...

> Any simple ideas that don't involve 'IF'

Fri, 06 Jan 2006 20:15:32 GMT
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

Quote:
> 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 max(1,2,3,4)
> etc...

> Any simple ideas that don't involve 'IF'

Fri, 06 Jan 2006 20:15:37 GMT
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 max(1,2,3,4)
etc...

Any simple ideas that don't involve 'IF'

Fri, 06 Jan 2006 20:01:04 GMT
MAX of two values

You can use a UDF:

create function dbo.MyMax
(

)
returns int
as
begin

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
Toronto, ON Canada
www.pinnaclepublishing.com/sql

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 max(1,2,3,4)
etc...

Any simple ideas that don't involve 'IF'

Fri, 06 Jan 2006 20:08:18 GMT
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
------------
Please reply only to the newsgroup
--

Fri, 06 Jan 2006 20:14:10 GMT
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

Quote:
>-----Original Message-----
>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 max(1,2,3,4)
>etc...

>Any simple ideas that don't involve 'IF'
>.

Fri, 06 Jan 2006 20:33:41 GMT
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

Quote:
> 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

Fri, 06 Jan 2006 20:37:18 GMT

 Page 1 of 1 [ 7 post ]

Relevant Pages

Powered by phpBB® Forum Software