CODEWITHSUNDEEP
sqloracle-databaseoracle11g
thePoly_glot
thePoly_glot

Reputation: 135

ORACLE get rows with condition value equals something but not equals to anything else

I have rows that look like . 

OrderNo     OrderStatus     SomeOtherColumn

A               1               
A               1 
A               3 
B               1                X
B               1                Y
C               2
C               3 
D               2

I want to return all orders that have only one possible value of orderstatus. For e.g Here order B has only order status 1 SO result should be

B 1 X
B 1 Y

Notes:

  1. Rows can be duplicated with same order status. For e.g. B here.
  2. I am interested in the order having a very peculiar status for e.g. 1 here and not having any other status. So if B had a status of 3 at any point of time it is disqualified.

Upvotes: 0

Views: 2025

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t.orderno = t2.orderno and t.OrderStatus = t2.OrderStatus
                 );

If you just want the orders where this is true, you can use group by and having:

select orderno
from t
group by orderno
having min(OrderStatus) = max(OrderStatus);

If you only want a status of 1 then add max(OrderStatus) = 1 to the having clause.

Upvotes: 2

kfinity
kfinity

Reputation: 9091

Just wanted to add something to Gordon's answer, using a stats function:

select orderno
from t
group by orderno
having variance(orderstatus) = 0;

Upvotes: 0

user5683823
user5683823

Reputation:

Here is one way to do it. It does not handle the case where the status can be NULL; if that is possible, you will need to explain how you want it handled.

SQL> create table test_data  ( orderno, status, othercol ) as (
  2      select 'A', 1, null from dual union all
  3      select 'A', 1, null from dual union all
  4      select 'A', 3, null from dual union all
  5      select 'B', 1, 'X'  from dual union all
  6      select 'B', 1, 'Y'  from dual union all
  7      select 'C', 2, null from dual union all
  8      select 'C', 3, null from dual union all
  9      select 'D', 2, null from dual
 10  );

Table created.

SQL> variable input_status number
SQL> exec :input_status := 1

PL/SQL procedure successfully completed.

SQL> column orderno format a8
SQL> column othercol format a8

SQL> select orderno, status, othercol
  2  from   (
  3           select t.*, count(distinct status) over (partition by orderno) as cnt
  4           from   test_data t
  5         )
  6  where  status = :input_status
  7    and  cnt    = 1
  8  ;

ORDERNO      STATUS OTHERCOL
-------- ---------- --------
B                 1 X
B                 1 Y

One way to handle NULL status (if that may happen), if in that case the orderno should be rejected (not included in the output), is to define the cnt differently:

count(case when status != :input_status or status is null then 1 end) 
      over (partition by orderno) as cnt

and in the outer query change the WHERE clause to a single condition,

where cnt = 0

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Count distinct OrderStatus partitioned by OrderNo and show only rows where number equals one:

select OrderNo, OrderStatus, SomeOtherColumn 
  from ( select t.*, count(distinct orderstatus) over (partition by orderno) cnt 
           from t )
  where cnt = 1

SQLFiddle demo

Upvotes: 0

Related Questions