max of ("...","...","..")

max of ("...","...","..")

Post by John » Thu, 10 Jun 1999 04:00:00



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

How can I do this in SQL?

 
 
 

max of ("...","...","..")

Post by Sean Bair » Thu, 10 Jun 1999 04:00:00


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

 
 
 

max of ("...","...","..")

Post by BPMargoli » Thu, 10 Jun 1999 04:00:00


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

 
 
 

max of ("...","...","..")

Post by Gabriel Prefontai » Fri, 11 Jun 1999 04:00:00




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

 
 
 

max of ("...","...","..")

Post by Sean Bair » Fri, 11 Jun 1999 04:00:00


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 :-)
 
 
 

max of ("...","...","..")

Post by BPMargoli » Fri, 11 Jun 1999 04:00:00


As I said in my original post ...

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

Thanks for the fix.

BPM




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

 
 
 

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

Hello
It is probably very easy, but I can't figure it out:
A field in a table on my MSSQL-server is at decimal-field, but in a view I
would like to use the field as an integer-field. I have tryed the SQL-code:

SELECT Int([Tabel1]![Felt2]) AS Udtryk1
FROM Tabel1;

but it dosn't work ! How do I do that ?

Thanks, Mads.

2. Delete SELECTion without confirmation?

3. "."and ","

4. Customizable forms at runtime

5. chart create in delphi 1.0 with dbase III data

6. The ""string""

7. CPU 100% use

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

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

10. Record.open "", "URL=..."

11. """ IT MAY SAVE YOUR LIFE""""