Greatest function in TSQL - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Greatest function in TSQL

Author

Topic

smithani
Starting Member

42 Posts

Posted - 2007-08-20 : 23:16:09

Hi All,
I need to find the highest of two numbers and lowest of the two numbers.
I read on the web that Greatest and least functions in sql can help me do that.
but when i use
greatest in my query it gives me this error

GREATEST is not a recognized built-in function name.
all I have is

declare @first int, @second int
set @first='12'
set @second='14'
select GREATEST(@first,@second)

Can somebody point me in the right direction.
Thanks so much

ujb
Starting Member

8 Posts

Posted - 2007-08-20 : 23:34:31

What about something like this if all you want is the results from a single select using ints ?

declare @first int, @second int
set @first=12
set @second=14
select CASE WHEN @first >= @second THEN @first ELSE @second END

ujb
Starting Member

8 Posts

Posted - 2007-08-20 : 23:36:16

Oops, note that the above will not handle NULLS at all well :-)

ujb
Starting Member

8 Posts

Posted - 2007-08-21 : 00:34:16

...another lazy variation that avoids writing a function and handles NULLs (by ignoring them)...

declare @first int, @second int
set @first=12
set @second=NULL

SELECT MAX(myint) as myint FROM (SELECT @first myint UNION ALL SELECT @second myint) a

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-08-21 : 00:42:48

i am assuming this is for homework. I will not answer your question, but I'll give you a hint.

look at the TOP function. keep in mind that TOP works with ORDER BY in your SELECT statement.

-ec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 02:50:24

[code]SELECT CASE
WHEN @First IS NULL AND @Second IS NULL THEN NULL
WHEN @First IS NULL THEN @Second
WHEN @Second IS NULL THEN @First
WHEN @First > @Second THEN @First
ELSE @Second
END AS Greatest[/code]

E 12°55'05.25"
N 56°04'39.16"

smithani
Starting Member

42 Posts

Posted - 2007-08-21 : 09:29:54

Thanks Guys for your help.SO there is no built in function in TSQL to do that.
To Clarify this is not for homework, for self improvement.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-21 : 09:32:54

I created this link just for you.

MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 09:38:08

I thought the greatest function in SQL Server was either bcp or xp_cmdshell

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources