How to display the duplicates from a table?

Question

I have problem. I trying display duplicates from table. My code in pl sql

SELECT intermediary_nr, beneficiary_role, contract_nr
  FROM (SELECT *
          from (select intermediary_nr,
                       beneficiary_role,
                       max(contract_nr) contract_nr
                  from boscs.atcs_commission_beneficiary
                 where beneficiary_role = 'LEAD'
                   and intermediary_nr is not null
                 group by intermediary_nr, beneficiary_role
                 ORDER BY dbms_random.value)

        union all
        SELECT *
          from (select intermediary_nr,
                       beneficiary_role,
                       max(contract_nr) contract_nr
                  from boscs.atcs_commission_beneficiary
                 where beneficiary_role = 'SUP_FOR_LEAD'
                   and intermediary_nr is not null
                 group by intermediary_nr, beneficiary_role
                 ORDER BY dbms_random.value)

        union all
        SELECT *
          from (select intermediary_nr,
                       beneficiary_role,
                       max(contract_nr) contract_nr
                  from boscs.atcs_commission_beneficiary
                 where beneficiary_role = 'COAGENT'
                   and intermediary_nr is not null
                 group by intermediary_nr, beneficiary_role
                 ORDER BY dbms_random.value))

Select intermediary_nr, beneficiary_role, contract_nt if a.contract_nr = b.contract_nr = c.contract_nr

this relation me interested. I please about tips on how to solve this. Are there any ideas? I want to display only the information that is the same contract_nr for a, b, c. The rest do not. Does anyone know how to complete these questions?

Answer 1

If I understand the request, you want to see only those rows that have the same contract in all three result sets. If that is the case, no problem.

First, let's tighten up the query somewhat. You have seven(!) SELECT statements where only one is needed.

select  intermediary_nr, beneficiary_role, max(contract_nr) contract_nr
from    boscs.atcs_commission_beneficiary
where   beneficiary_role in( 'LEAD', 'SUP_FOR_LEAD', 'COAGENT' )
    and intermediary_nr is not null
group by intermediary_nr, beneficiary_role;

I've omitted the order by clause because it doesn't seem to serve any purpose.

This will generate a result set kinda like this:

intermediary_nr  beneficiary_role  contract_nr
===============  ================  ===========
            ...  ...               ...
             10  LEAD              100
             10  SUP_FOR_LEAD      100
             20  LEAD              101
             20  SUP_FOR_LEAD      101
             20  COAGENT           101
            ...  ...               ...

For the rest of this, I'm making the assumption that an output like the following is not possible:

intermediary_nr  beneficiary_role  contract_nr
===============  ================  ===========
            ...  ...               ...
             10  LEAD              100
             10  SUP_FOR_LEAD      100
             20  LEAD              101
             20  SUP_FOR_LEAD      101
             20  COAGENT           101
             30  COAGENT           101
            ...  ...               ...

I don't know what your data looks like, so I don't know if that is a valid assumption. Only you can answer that.

Now you only want the rows where the same contract number appears for all three roles. In my example, that would be 101. If we feed the results of the first query into a second query, we can perform more grouping for that result:

select   contract_nr
from     <query above>
group by contract_nr
having count(*) = 3;

This will give you a list of the contracts you are looking for. Union those back to the first query and you have the result you are (I hope) looking for:

with
ContractByRole( intermediary_nr,  beneficiary_role,  contract_nr )as(
  select  intermediary_nr, beneficiary_role, max(contract_nr) contract_nr
  from    boscs.atcs_commission_beneficiary
  where   beneficiary_role in( 'LEAD', 'SUP_FOR_LEAD', 'COAGENT' )
      and intermediary_nr is not null
  group by intermediary_nr, beneficiary_role
),
AllRoles( contract_nr )as(
  select   contract_nr
  from     ContractByRole
  group by contract_nr
  having count(*) = 3
)
select  cbr.*
from    ContractByRole cbr
join    AllRoles       ar
    on  ar.contract_nr = cbr.contract_nr;

which generates:

intermediary_nr  beneficiary_role  contract_nr
===============  ================  ===========
             20  LEAD              101
             20  SUP_FOR_LEAD      101
             20  COAGENT           101

If the intermediary value does not follow the pattern I show, the query will have to be tweaked a little to compensate, but that will not be difficult.