Count double occurrences in order list

Question

I have a list of orders, I need to find which ones occur with code 47 more than once with different users. For example:

ORDER_ID  CODE USER
111       47   1 
111       47   2
222       47   1
333       47   1
333       47   2
444       47   1   

The expected result is 111 and 333.

How can I accomplish this?

Regards

Answer 1

I think you want aggregation and having:

select order_id
from orders o
where code = 47
group by order_id
having min(user) <> max(user);

You can also express the having as:

having count(distinct user) >= 2

Answer 2

You can try below -

select order_id from tablename
group by order_id
having count(distinct user)>=1

Answer 3

You can do it via row_number() as well

    Select distinct order_id from

   (select order_id, code, row_number() 
    over
   ( Partition by order_id, code 
     Order by order_id, code) rn
     from 
    tablename
    where user in (1,2)
    ) where rn>=1

But I guess you already have a user column hence i dont think you require extra manipulation

   Select orderid, code from table 
  Group by orderid, code having 
   max(distinct user) >=1