CODEWITHSUNDEEP
oracle-database
Stephanie Monteiro
Stephanie Monteiro

Reputation: 9

Select onle group that have positivo and negative number in plsql

I have a table that have date grouped. I need to select only groups that have positive and negative value inside. For example:

id value1 
2  7
2  8
2  -1
3  3
3  4
4  -1
4  -2
5  7
5  -5

the result should be

id value1 
2  7
2  8
2  -1
5  7
5  -5

because the group with id 3 just have positive number and the group with id 4 just have negative number.

any idea how can I do it using case (when then) in a select or using if else inside a function. Or any other idea?

Upvotes: 0

Views: 651

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Try this.

select id,value1 FROM
( 
select  t.*,
count( DISTINCT SIGN (value1 )  ) OVER (PARTITION BY id ) n
               from yourtable t
)    WHERE n = 2
;

The Sign() function gives 1 for positive and -1 for negative numbers.

DEMO

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

If you group by the ID, you can use the aggregate functions MIN and MAX to find out if there are both positive and negative values. You need to decide how to treat 0 though... I have treated it as positive below :)

with your_table as(
-- Your example data here, this is not really part of the solution
   select 2 as id, 7  as value1 from dual union all
   select 2 as id, 8  as value1 from dual union all
   select 2 as id, -1 as value1 from dual union all
   select 3 as id, 3  as value1 from dual union all
   select 3 as id, 4  as value1 from dual union all
   select 4 as id, -1 as value1 from dual union all
   select 4 as id, -2 as value1 from dual union all
   select 5 as id, 7  as value1 from dual union all
   select 5 as id, -5 as value1 from dual
)
select *
  from your_table
 where id in(select id
               from your_table
              group by id 
             having min(value1) < 0
                and max(value1) >= 0);

Upvotes: 0

Related Questions