More than one occurrence from table

Question

create table customer (cif number, name varchar(20),mobile number);

insert into table customer values(121,'ANT',789);    
insert into table customer values(122,'ANT',789);   
insert into table customer values(123,'ENT',789);    
insert into customer values(124,'ENT',789);    
insert into customer values(125,'BEE',123);    
insert into customer values(126,'BEE',123);    
insert into customer values(127,'BRO',789);    
insert into customer values(128,'FIO',789);    
commit;

I want retrieve data from customer table based on name and mobile more than one occurrences.

Can anyone help me out

Result like

Does this answer your question? [How do I find duplicate values in a table in Oracle?](https://stackoverflow.com/questions/59232/how-do-i-find-duplicate-values-in-a-table-in-oracle) — nbk, Feb 14 '23 at 12:47

score 2 · Accepted Answer · answered Feb 14 '23 at 12:45

2

You can use COUNT() aggregation as Analytic function along with grouping by those columns through use of PARTITION BY clause as

SELECT cif, name, mobile
  FROM (SELECT c.*,
               COUNT(*) OVER (PARTITION BY name, mobile) AS cnt
          FROM customer c )
 WHERE cnt > 1

Demo

answered Feb 14 '23 at 12:45

Barbaros Özhan

59,113
10
31
55

Have a nice study and day @BalaS – Barbaros Özhan Feb 14 '23 at 12:56

score 1 · Answer 2 · answered Feb 14 '23 at 12:50

Source:

SQL> select * From customer;

       CIF NAME                     MOBILE
---------- -------------------- ----------
       121 ANT                         789
       122 ANT                         789
       123 ENT                         789
       124 ENT                         789
       125 BEE                         123
       126 BEE                         123
       127 BRO                         789
       128 FIO                         789

8 rows selected.

Based on result you posted, you're looking for rows that aren't unique per name and mobile. If that's so, here's another option:

SQL> select *
  2  from customer a
  3  where exists (select null
  4                from customer b
  5                where b.name = a.name
  6                  and b.mobile = a.mobile
  7                group by b.name, b.mobile
  8                having count(*) > 1
  9               );

       CIF NAME                     MOBILE
---------- -------------------- ----------
       121 ANT                         789
       122 ANT                         789
       123 ENT                         789
       124 ENT                         789
       125 BEE                         123
       126 BEE                         123

6 rows selected.

SQL>

More than one occurrence from table

2 Answers2