CODEWITHSUNDEEP
sqloracle-database
Devadas Wagle
Devadas Wagle

Reputation: 37

selecting a row only if it satisfies another criteria for the similar row

Table orders

Status_id Order_number 
100        ord1 
200        ord2 
100        ord3 
300        ord2 
100        Ord4 
400        ord2 
200        ord1
200        ord3

I need all the orders that are with the Status_id=100 or 200 BUT if the same orders have Status_id=400, it should not be selected. In the example above, ord1,ord3, ord4 should be selected. But Ord2 should not be selected as it is also with status_id=400.

Upvotes: 0

Views: 57

Answers (2)

user5683823
user5683823

Reputation:

If you need the full details of every row, rather than just the order numbers, then you need to use analytic functions rather than aggregates; in this case, a conditional analytic count on rows where test_data=400.

with
     test_data ( status_id, order_number ) as (
       select 100, 'ord1' from dual union all 
       select 200, 'ord2' from dual union all  
       select 100, 'ord3' from dual union all 
       select 300, 'ord2' from dual union all 
       select 100, 'ord4' from dual union all 
       select 400, 'ord2' from dual union all 
       select 200, 'ord1' from dual union all 
       select 200, 'ord3' from dual
     )
-- end of test data (not part of the query); SQL query begins below this line
select   status_id, order_number
from     ( select status_id, order_number,
                  count(case when status_id = 400 then 1 end)
                             over (partition by order_number) as st_400_cnt
           from   test_data
         )
where    status_id in (100, 200)
  and    st_400_cnt = 0
order by order_number, status_id   --  if needed
;

STATUS_ID ORDER_NUMBER
--------- ------------
      100 ord1
      200 ord1
      100 ord3
      200 ord3
      100 ord4

5 rows selected.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

This is a type of problem that is readily solved with group by and having:

select order_number
from orders
group by order_number
having sum(case when status in (100, 200) then 1 else 0 end) > 0 and
       sum(case when status = 400 then 1 else 0 end) = 0;

Upvotes: 2

Related Questions