Min function

7 Jul 2005 2:01 PM

RP

Hi,
This is my scenario .... I have 2 columns .. with values like this.
col1     col2
3432     545465
0          6545656
543        54654
54          6456456456
54            45

i want to find out the non-zero minimum out of 2 columns.
so i wrote a fn. which takes min(col1),min(col2) and gives me the output.
but in this case i dont want to send 0,45 but i want to send non-zero minumum
so it would be 54,45. how can i do this ?

Thanks
RP

7 Jul 2005 2:18 PM

John Bell

Hi

You could try something like SELECT MIN(NULLIF(col1,0)), MIN(NULLIF(col2,0))
....

John

Show quote

"RP" wrote:

> Hi,
> This is my scenario .... I have 2 columns .. with values like this.
> col1     col2
> 3432     545465
> 0          6545656
> 543        54654
> 54          6456456456
> 54            45
>
> i want to find out the non-zero minimum out of 2 columns.
> so i wrote a fn. which takes min(col1),min(col2) and gives me the output.
> but in this case i dont want to send 0,45 but i want to send non-zero minumum
> so it would be 54,45. how can i do this ?
>
> Thanks
> RP
>

7 Jul 2005 7:12 PM

--CELKO--

SELECT CASE WHEN MIN(col1) <= MIN(col2)
THEN MIN(col1) ELSE MIN(col2) END AS smallest
FROM (SELECT NULLIF (col1, 0), NULLIF (col2, 0)
FROM Foobar) AS F1 (col1,col2) ;

Get rid of the zeros by making them NULL. The CASE expression will
work if all the THEN clauses are aggregates.