How do I find negative high and positive low in Oracle?
I have a column of type number
. It has both positive and negative meanings. I need to get 4 values: positive high, positive low, negative high, negative low:
a) To get a positive maximum: I can use the Check script
select max(cola) from test;
b) To get negative minimum: I can use Check script
select min(cola) from test;
I have two questions:
1) Now I'm not sure how to get the other two values. Guide me to find out what
2) Meanwhile, while trying this, I got another doubt. When I have a column of type varchar2
and it has numbers as value. I am performing the above operation on this column. The positive high is the same as above. But the negative low is quiet. Check the script here . Why is there no proper implicit conversion here. Does anyone explain the reason for this?
source to share
In question 1, you can easily use case to determine what values you are using min / max. For example:.
select max(case when cola >= 0 then cola end) max_positive,
min(case when cola >= 0 then cola end) min_positive,
max(case when cola < 0 then cola end) max_negative,
min(case when cola < 0 then cola end) min_negative
from test;
In question 2, when you do min / max on something that is varchar, you are going to be doing string comparisons, not number comparisons. You must explicitly convert the values to numbers, as Oracle doesn't know what you expected an implicit conversion to happen. And you shouldn't rely on implicit conversions anyway. For example:.
select max(case when to_number(cola) >= 0 then to_number(cola) end) max_positive,
min(case when to_number(cola) >= 0 then to_number(cola) end) min_positive,
max(case when to_number(cola) < 0 then to_number(cola) end) max_negative,
min(case when to_number(cola) < 0 then to_number(cola) end) min_negative
from test1;
Here's a SQLFiddle for both cases.
NB I have explicitly split both negative and positive values (I put 0 with positive numbers, you need to decide how you want to handle strings with a value of 0!), Just in case there were no negative numbers or no positive numbers.
source to share
use case expressions in aggregation functions.
eg.
max (when cola <0, then end of cola) max_neg
min (cola) min_neg - no need for case statement here
Oracle 11g R2 schema setup :
Create table test(COLA number);
Insert into test values(1);
Insert into test values(50);
Insert into test values(-65);
Insert into test values(25);
Insert into test values(-2);
Insert into test values(-8);
Insert into test values(5);
Insert into test values(-11);
Create table test1(COLA varchar2(10));
Insert into test1 values('1');
Insert into test1 values('50');
Insert into test1 values('-65');
Insert into test1 values('25');
Insert into test1 values('-2');
Insert into test1 values('-8');
Insert into test1 values('5');
Insert into test1 values('-11');
Request 1 :
select
max(case when cola < 0 then cola end) max_neg_cola
, min(cola)
, min(case when cola > 0 then cola end) min_pos_cola
, max(cola)
from test
Results :
| MAX_NEG_COLA | MIN(COLA) | MIN_POS_COLA | MAX(COLA) |
|--------------|-----------|--------------|-----------|
| -2 | -65 | 1 | 50 |
source to share
1) Now I'm not sure how to get the other two values.
Use a CASE expression .
SQL> SELECT MAX(
2 CASE
3 WHEN cola >= 0
4 THEN cola
5 END) max_positive,
6 MIN(
7 CASE
8 WHEN cola >= 0
9 THEN cola
10 END) min_positive,
11 MAX(
12 CASE
13 WHEN cola < 0
14 THEN cola
15 END) max_negative,
16 MIN(
17 CASE
18 WHEN cola < 0
19 THEN cola
20 END) min_negative
21 FROM test;
MAX_POSITIVE MIN_POSITIVE MAX_NEGATIVE MIN_NEGATIVE
------------ ------------ ------------ ------------
50 1 -2 -65
SQL>
2) Meanwhile, while trying this, I got another doubt. When I have some varchar2 type column and it has numbers as value. I am performing the above operation on this column. The positive high is the same as above. But the negative low sounds strange.
First you need to convert STRING to NUMBER and then use the same query. To type less to_number each time, you can use the WITH clause .
NOTE You should only have numbers in this column, not alphanumeric . Also, be sure to get a message . ORA-01722: invalid number
SQL> WITH t AS
2 ( SELECT to_number(cola) cola FROM test1
3 )
4 SELECT MAX(
5 CASE
6 WHEN cola >= 0
7 THEN cola
8 END) max_positive,
9 MIN(
10 CASE
11 WHEN cola >= 0
12 THEN cola
13 END) min_positive,
14 MAX(
15 CASE
16 WHEN cola < 0
17 THEN cola
18 END) max_negative,
19 MIN(
20 CASE
21 WHEN cola < 0
22 THEN cola
23 END) min_negative
24 FROM t;
MAX_POSITIVE MIN_POSITIVE MAX_NEGATIVE MIN_NEGATIVE
------------ ------------ ------------ ------------
50 1 -2 -65
SQL>
source to share