Getting max value from a row containing zeros,negative

I have a query which calculate the avg I have used union all in the query so to get the data i have finally used max function.

As union returns result something like this:-

col 1      col2
1            0
2            0
3            0
4            0
0            -1
0            2
0            3
0            4

so when i do max it returns me 0 instead of -1

my query goes like this :-

select max(col1), max(col2)
from mytbl

i get resultset like this:-

max(col1)      max(col2)
1               0
2               2
3               3
4               4

Can anyone tell me how can i can max from 0 and a negative value in a que开发者_高级运维ry.

If you really want to do this using MAX() (although I think that you should think about the whole query more), try replacing the zeros (which apparently "do not count") by NULL. MAX() will disregard the NULL's and compute the maximum of the other elements.

Does this help?

select max(t1.col1), max(t2.col2) from mytbl t1 left join mytbl t2 on t2.col2 <= 0 where t1.col1 <= 0;

Your query

select max(col1), max(col2)
from mytbl

can not select multiple rows, because MAX is aggregation function.