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
 
 [ 7 post ] 

 Relevant Pages 

1. Finding the MAX of two values

2. Select max value out of two columns

3. Select MAX(Value) and other non Agg. values

4. Getting MAX value from aggregate value

5. SP_Configure truncates the values for min and max for large values

6. Related values lists with two values does not work with keyboard

7. MS Remote and max of two concurrent connections

8. Selecting 'Max' value AND next highest 'Max' value

9. SQL quest:after select the max(value) of a table, how to know that max value's rowid?

10. Select field from row with max value

11. retrieving other column value with MAX function

12. Determine the Max(IDENTITYCOL) value for all my tables


 
Powered by phpBB® Forum Software