Selection based on multiple conditions

I have a requirement to select Customers based on the Channel and Organisation i.e., the order of selection of Customers should be based on Channel – 10,40,20,30 and all the organisations for that customer needs to selected as well.

For Eg if a customer has a Channel 10 and 40 – then Channel 10 takes a priority over 40 and so on. Also, For every Customer, if they have 2 Organisations, then they both need to be selected.

Hope I am clear. Please can somebody help me with this query.

Answer

You can use Oracle’s KEEP FIRST to apply an order of precedence:

select
  min(channel) keep (dense_rank first order by 
    case channel when 10 then 1 when 40 then 2 when 20 then 3 else 4 end),
  customer,
  org
from mytable
group by customer, org
order by customer, org;

The same can be achieved with the standard-complient ROW_NUMBER:.

select channel, customer, org
from
(
  select
    t.*
    row_number() over (partition by customer, org
                       order by case channel when 10 then 1
                                             when 40 then 2
                                             when 20 then 3
                                             else 4 end) as rn,
  from mytable t
)
where rn = 1
order by customer, org;

Add a WHERE clause, if you want to restrict this to certain customers, channels or organizations.