Help with MAX function - not aggregates

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 2005 Forums

Transact-SQL (2005)

Help with MAX function - not aggregates

Author

Topic

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-12-18 : 17:47:42

Is there a function that returns the highest value of two arguments supplied?? Something like the MAX(arg1, arg2) functions typically used in C# or Java.

Example usage:

SELECT MAXIMUM(1, 3)
--returns 3

SELECT MAXIMUM(-1, -10)
--returns -1

SELECT MAXIMUM(5, MAXIMUM(1, 7))
--returns 7

I know it isn't a difficult function to code myself, but I don't have privileges and don't want to go through the effort if something similar exists.

--Thanks--

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-18 : 17:51:46

SELECT CASE WHEN arg1 > arg2 THEN arg1 ELSE arg2 END as HigherArg

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-12-18 : 21:59:40

I Prefer to use something along the lines of



select max(a.MyVal) FROM
(
select 5 as myval union all
Select 1 Union all
select 7
) a

This way you can have unlimited values to referance. Here's a example of how to use in a query


Declare @Test table (NameField varchar(50),Number1 int ,Number2 int,Number3 int,Number4 int)

Insert Into @Test(NameField,Number1,Number2,Number3,Number4)
select 'John',2,5,87,1 Union all
select 'Pat',91,51,41,3 Union all
select 'Jake',3,5,81,2 Union all
select 'Debbie',101,99,7,5 Union all
select 'Tom',12,45,17,2


Select a.NameField,	( Select Max(aa.Myval) from 
						(
						Select a.Number1 as Myval union all
						Select a.Number2 union all
						Select a.Number3 union all
						Select a.Number4 
						) aa
					) as HighestValue
From
@Test a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources