function to return greater of two numbers

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

Transact-SQL (2000)

function to return greater of two numbers

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-04-18 : 13:51:44

Is there a built-in function that returns the greater of two numbers? In C# you use Math.Max(1,2) and if I could do it that way in T-SQL it would make my query a lot simpler because the values I'm comparing are in two different columns.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-18 : 13:55:10

No. But you can create the function easily using UDF.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 14:03:12

DECLARE @n1 INT, @n2 INT

SELECT @n1 = 6, @n2 = 10

SELECT (@n1 + @n2 + ABS(@n1 - @n2) / 2 AS MaxNumber, (@n1 + @n2 - ABS(@n1 - @n2) / 2 AS MinNumber

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-18 : 14:45:35

that seems like a lot of operations to find the max. why not just this:

select case when @n1 > @n2 then @n1 else @n2 end

www.elsasoft.org

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-04-18 : 16:00:41

Thanks all, I ended up using a case statement. Maybe MS should consider adding some new functions in the next release of T-SQL so I don't have to create them myself.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-18 : 18:13:24

quote:
Originally posted by influent

Thanks all, I ended up using a case statement. Maybe MS should consider adding some new functions in the next release of T-SQL so I don't have to create them myself.

Go ahead and send them your suggestions. I'm sure they'll be glad to hear your feedback.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 00:49:53

quote:
Originally posted by jezemine

that seems like a lot of operations to find the max.

Yes, I know. I forgot to paste the link to the "Best Practices" page from "YouKnowWho", who recently got 3 pages dedicated to himself.

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-19 : 02:01:29

quote:
Originally posted by Peso
I forgot to paste the link to the "Best Practices" page from "YouKnowWho", who recently got 3 pages dedicated to himself.

hehe - I think that oaf would have used a triply-nested cursor, and still returned the avg instead of the max!

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources