sql - How to find Negative maximum and positive minimum in Oracle? -

i have column of type number. has both positive , negative values. need 4 values : positive maximum,positive minimum, negative maximum,negative minimum:

a)to positive maximum : can use check out fiddle

select max(cola) test; 

b)to negative minimum: can use check out fiddle

select min(cola) test; 

i have 2 question here:

1)now i'm not sure how other 2 values. guide me that

2)meanwhile while trying got doubt. when have column of type varchar2 , has numbers value. i'm performing above operation in column. positive maximum same above. negative minimum quiet weird. check fiddle here .why there no proper implicit conversion taking place here. pls explain reason behind this?

for question 1, can use case determine values min/max on. e.g.:

select max(case when cola >= 0 cola end) max_positive,        min(case when cola >= 0 cola end) min_positive,        max(case when cola < 0 cola end) max_negative,        min(case when cola < 0 cola end) min_negative   test; 

for question 2, when min/max on that's varchar, you're going doing string comparisons, not number comparisons. have explicitly convert values numbers, since oracle doesn't know expected implicit conversion take place. , shouldn't rely on implicit conversions anyway. e.g.:

select max(case when to_number(cola) >= 0 to_number(cola) end) max_positive,        min(case when to_number(cola) >= 0 to_number(cola) end) min_positive,        max(case when to_number(cola) < 0 to_number(cola) end) max_negative,        min(case when to_number(cola) < 0 to_number(cola) end) min_negative   test1; 

here's sqlfiddle both cases.

n.b. i've explicitly split out both negative , positive values (i stuck 0 in positive numbers; you'll have decide how want treat rows value of 0!), in case there no negative numbers or no positive numbers.