max of ("...","...","..") 
Author Message
 max of ("...","...","..")
I want to return (for each row) the max of three columns, outputing one
column.

How can I do this in SQL?



Sun, 25 Nov 2001 03:00:00 GMT
 max of ("...","...","..")

Quote:
> I want to return (for each row) the max of three columns, outputing one
> column.

> How can I do this in SQL?

Hmm... maybe with a searched case?

CASE
    WHEN Col1 > Col2 THEN Col1
    WHEN Col2 > Col3 THEN Col2
    ELSE   Col3
END

Sorry, don't have a SQL Server in front of me, so I couldn't test it.

Sean



Sun, 25 Nov 2001 03:00:00 GMT
 max of ("...","...","..")
Sean,

Having posted enough "broken code" on this newsgroup myself, forgive me for
pointing out that your CASE won't work quite the way you want it to.

Specifically, consider the instance when Col1 is greater than Col2, but is
less than Col3. Your case will return Col1 instead of Col3.

Might I suggest the following:

 CASE
     WHEN Col1 > Col2  AND Col1 > Col3 THEN Col1
     WHEN Col2 > Col1  AND Col2 > Col3 THEN Col2
     ELSE   Col3
 END


Quote:
> > I want to return (for each row) the max of three columns, outputing one
> > column.

> > How can I do this in SQL?

> Hmm... maybe with a searched case?

> CASE
>     WHEN Col1 > Col2 THEN Col1
>     WHEN Col2 > Col3 THEN Col2
>     ELSE   Col3
> END

> Sorry, don't have a SQL Server in front of me, so I couldn't test it.

> Sean



Sun, 25 Nov 2001 03:00:00 GMT
 max of ("...","...","..")


Quote:
>Having posted enough "broken code" on this newsgroup myself, forgive me for
>pointing out that your CASE won't work quite the way you want it to.

Ditto.

Quote:
>Specifically, consider the instance when Col1 is greater than Col2, but is
>less than Col3. Your case will return Col1 instead of Col3.

>Might I suggest the following:

> CASE
>     WHEN Col1 > Col2  AND Col1 > Col3 THEN Col1
>     WHEN Col2 > Col1  AND Col2 > Col3 THEN Col2
>     ELSE   Col3
> END

Consider the case where Col1 = Col2 and Col1 > Col3.  Your statement
would return Col3.  

The correct version is:

CASE
    WHEN Col1 >= Col2  AND Col1 >= Col3 THEN Col1
    WHEN Col2 >= Col1  AND Col2 >= Col3 THEN Col2
    ELSE   Col3
END

I hope.

Gaby



Mon, 26 Nov 2001 03:00:00 GMT
 max of ("...","...","..")

Quote:
> Having posted enough "broken code" on this newsgroup myself, forgive me
for
> pointing out that your CASE won't work quite the way you want it to.

D'oh!   That's what I get for opening my mouth before I tested it :-)


Mon, 26 Nov 2001 03:00:00 GMT
 max of ("...","...","..")
As I said in my original post ...

"Having posted enough "broken code" on this newsgroup myself" ...

Thanks for the fix.

BPM


Quote:


> >Having posted enough "broken code" on this newsgroup myself, forgive me
for
> >pointing out that your CASE won't work quite the way you want it to.

> Ditto.

> >Specifically, consider the instance when Col1 is greater than Col2, but
is
> >less than Col3. Your case will return Col1 instead of Col3.

> >Might I suggest the following:

> > CASE
> >     WHEN Col1 > Col2  AND Col1 > Col3 THEN Col1
> >     WHEN Col2 > Col1  AND Col2 > Col3 THEN Col2
> >     ELSE   Col3
> > END

> Consider the case where Col1 = Col2 and Col1 > Col3.  Your statement
> would return Col3.

> The correct version is:

> CASE
>     WHEN Col1 >= Col2  AND Col1 >= Col3 THEN Col1
>     WHEN Col2 >= Col1  AND Col2 >= Col3 THEN Col2
>     ELSE   Col3
> END

> I hope.

> Gaby



Mon, 26 Nov 2001 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. How to make a "decimal"-field to an "integer"-field

2. "."and ","

3. "."and ","

4. The ""string""

5. aReport."Field".SetFilter("this")?

6. The+instruction+at+"0x1f90db9b"+referenced+memory+at+"0x7fa03794"+The+memory+cou

7. Record.open "", "URL=..."

8. """ IT MAY SAVE YOUR LIFE""""

9. "IIF" , "AND", and "aliases"

10. ""URGENT "" Inserting "For xml explicit" results to a Temp table

11. dr("field").toString returns "400.0000" instead of "400"


 
Powered by phpBB® Forum Software