Select Higher of Two Values... 2

[Mr3Putt]

Seems to me that there was a funtion somewhere that returned the greater of two parameters, like:

biggerOf(1, 5) = 5... or
biggerOf(0, -1) = 0

I want to set a zero baseline, but I cannot recall the quick way (if ever there was one).

dang, i'm gettin' old

[red]Note:[/red] [gray]The above comments are the opinionated ravings of Mr3Putt. As such, Mr3Putt accepts no responsibility for damages, real or contrived, resulting from acceptance of his opinions as fact.[/gray]

 

[Pattycake245]

max()

Tim

 

[donutman]

[Blue]SELECT[/Blue] [Blue]CASE[/Blue] [Blue]WHEN[/Blue] Val1[Gray]>[/Gray]Val2 [Blue]THEN[/Blue] Val1 [Blue]ELSE[/Blue] Val2 [Blue]END[/Blue]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

that CASE expression is going to get awfully cluttered as the number of terms increases beyond 2

standard SQL offers the scalar functions GREATEST and LEAST

too bad they're not implemented in SQL Server

(they are in MySQL -- neener, neener!)



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[donutman]

If you don't like clutter make it a recursive function (and bring your server to its knees). I agree with you rudy, that is a sorely needed function and I've heard nothing about it in regards to Yukon.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Recursive? Even this looks better:

select max(blah) 
from 
( select 5 as blah union select 6 union select 4 ) X

OK, this clutters exec plan with trivial UNIONs...

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Yeah, but how would you do it with columns. I assume that greatest() and least() works with them?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

mygreatest(myGreatest(myGreatest(a, b), c), ... )

Yuck.

Btw. how to do that recursively?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

You call Erik back from his honeymoon.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Mr3Putt]

donutman yeah, that the CASE statement, of course, works just fine... I was just hoping that something would shake loose from the great foggy recesses of my memory. Something closer to COALESCE, but checking for lower values instead of NULLs.

I guess I'm remembering wrong. It might've been in DYL-280... or SYNCSORT, or something. Rats.

I really didn't want to have to write CASE-WHEN-THEN-ELSE-END statements for multiple columns. GREATEST and LEAST would be GREAT functions, to say the LEAST.

[red]Note:[/red] [gray]The above comments are the opinionated ravings of Mr3Putt. As such, Mr3Putt accepts no responsibility for damages, real or contrived, resulting from acceptance of his opinions as fact.[/gray]

 

[donutman]

[Blue]CREATE[/Blue] [Blue]FUNCTION[/Blue] dbo.MyGreatest 
   [Gray]([/Gray]@V1 [Blue]int[/Blue][Gray],[/Gray] @V2 [Blue]int[/Blue][Gray],[/Gray] @V3 [Blue]int[/Blue] [Gray]=[/Gray] [Gray]NULL[/Gray][Gray],[/Gray] @V4 [Blue]int[/Blue] [Gray]=[/Gray] [Gray]NULL[/Gray][Gray])[/Gray] [green]
--- I was hoping that you could call the function out including all the parameters,
[/green][green]--- but it doesn't work like a Stored Proc.
[/green][Blue]RETURNS[/Blue] [Blue]int[/Blue]
[Blue]AS[/Blue]  
[Blue]BEGIN[/Blue]
   [Blue]DECLARE[/Blue] @MV [Blue]int[/Blue]
   [Blue]SET[/Blue] @MV[Gray]=[/Gray][Blue]CASE[/Blue] [Blue]WHEN[/Blue] @V1[Gray]>[/Gray]@V2  [Blue]THEN[/Blue] @V1 [Blue]ELSE[/Blue] @V2 [Blue]END[/Blue]
   [Blue]IF[/Blue] @V3 [Gray]<[/Gray][Gray]>[/Gray] [Gray]NULL[/Gray]
      [Blue]SET[/Blue] @MV [Gray]=[/Gray] dbo.MyGreatest [Gray]([/Gray]@MV[Gray],[/Gray] @V3[Gray],[/Gray] @V4[Gray],[/Gray] [Gray]NULL[/Gray][Gray])[/Gray]
   [Blue]RETURN[/Blue] @MV
[Blue]END[/Blue]

That's what I had in mind, vongrunt. Is there a way to make it a recursive SP, so that you don't have to include a value for every parameter? Alternative you could define My3Greatest, My4Greatest etc., but that'd be really sick.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Doh, SP dumb idea.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

I really didn't want to have to write CASE-WHEN-THEN-ELSE-END statements for multiple columns.

I couldn't resist, so here comes math hack for this specific case (zero baseline):

select ceiling(sign(somecolumn)/2.) * somecolumn

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

[Blue]SELECT[/Blue] SomeValue[Gray]*[/Gray][Fuchsia]Sign[/Fuchsia][Gray]([/Gray]SomeValue[Gray]+[/Gray][Fuchsia]Abs[/Fuchsia][Gray]([/Gray]SomeValue[Gray])[/Gray][Gray])[/Gray]

You beat me by SomeValue, but mine has more symmetry.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Actually...:

select (someValue + abs(someValue))/2.

Your turn

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

You sob!

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]