How to find Column with same (some x value) value repeated more than once? Needs to return those rows.
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 ;
select id, name, address, ph_nofrom contactswhere 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
select * from contacts c where c.name in ( select cc.name from contacts group by cc.name having count(1) > 1 );