How to find Column with same (some x value) value repeated more than once? Needs to return those rows.

Question

There is a table called contacts with columns id, name, address, ph_no etc.
I need to find out rows with the same name, if the rows count is more than 1, show those rows.

For example:
Table: contacts

id--------name--------address---------ph_no--------
111       apple       U.K             99*******
112       banana      U.S             99*******
123       grape       INDIA           99*******
143       orange      S.AFRICA        99*******
152       grape       KENYA           99*******

For the above table I need to get rows with same column name data like the below:

id--------name--------address---------ph_no--------
123       grape       INDIA           99*******
152       grape       KENYA           99*******

I need to get the rows based on the name what I given as argument like below example syntax:

select * from contacts where name='grape' and it's count(*) >1 return those rows.

How can I achieve the solution for above problem.

what is missing exactly from the answers already given? (what details are u looking for) — tbone, Feb 02 '12 at 11:49

Ben · Accepted Answer · 2011-12-01T16:38:00.067

12

As @vc74 suggests analytic functions would work work a lot better here; especially if your data has any volume.

select id, name, address, ph_no ...
  from ( select c.*, count(name) over ( partition by name ) as name_ct
           from contacts c )
 where name_ct > 1
       ;

EDIT

restricting on specific names the table contacts should really have an index on name and the query would look like this:

select id, name, address, ph_no ...
  from ( select c.*, count(name) over ( partition by name ) as name_ct
           from contacts c
          where name = 'grape' )
 where name_ct > 1
       ;

edited Dec 01 '11 at 16:38

answered Nov 30 '11 at 14:37

Ben

51,770
36
127
149

your code is working fine, but I need to pass the 'grape' as an argument in to query. – Chandra Sekhar Dec 01 '11 at 06:34
@azad you can add a where clause if you want. I'd add restrict on the sub-select and have an index on `name`. It does mean though that if you only have one instance of `where name = 'grape'` your query would return nothing? – Ben Dec 01 '11 at 09:05
it wasn't working. (wasn't returning rows which contain, that field 'grape' more than once). plz can you check once again!. – Chandra Sekhar Dec 01 '11 at 11:05
Sorry you were correct, you should partition by name to get the count by name. partitioning by 1 gets the total. – Ben Dec 01 '11 at 16:38
Hi this was very useful but now I want to do this with two columns, that is other than just this name, i need it with same name and ph_no. Is this possible if i put both these as counts and then add in the where clause with AND? – Tom Thomas Mar 12 '14 at 12:13
That should work @Tom, if you need both counts. If you need a single count over both of those columns change the partition clause. – Ben Mar 12 '14 at 12:46
I don't actually need the count but need all the records with these two columns having the same value, this method seems to work but am still testing it..taking time since its a large amount of data. – Tom Thomas Mar 12 '14 at 13:58
I had a problem using that method, because when i put two counts in the select and checked them in the where clause as count1>1 and count2>1 the problem i faced (might be due to the data) was it always returned a single row. And single row records were not supposed to be returned. Hence i made a workaround to neglect the single row result if and when there was only one row returned from the select using the row_number() and modifying the query by replacing the count() over by the following : (refer next comment) – Tom Thomas Mar 14 '14 at 06:35
row_number() over ( partition by tab.col1 order by tab.col1) as count1, row_number() over (partition by tab2.col2 order by tab2.col2) as count2 and then using the same where clause as before, now its working fine.. – Tom Thomas Mar 14 '14 at 06:35

score 3 · Answer 2 · answered Nov 30 '11 at 13:20

3

select id, name, address, ph_no
from contacts
where name in
(
  select name from contacts
  group by name
  having count(*) > 1
)

If you have access to Oracle's analytical functions there might be a more straightforward way

answered Nov 30 '11 at 13:20

vc 74

37,131
7
73
89

score 1 · Answer 3 · edited Dec 14 '15 at 08:06

1

select * 
from contacts c 
where c.name in ( select cc.name 
                  from contacts 
                  group by cc.name 
                  having count(1) > 1 );

edited Dec 14 '15 at 08:06

Arulkumar

12,966
14
47
68

answered Nov 30 '11 at 13:21

Zsolt Botykai

50,406
14
85
110

How to find Column with same (some x value) value repeated more than once? Needs to return those rows.

3 Answers3