How to find Column with same (some x value) value repeated morethan once? needs to return those rows. (in Oracle)

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.

Answers


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
       ;

Need Your Help

sit sentences on background lines

html css css3 background background-image

i just want to know if someone can help me with a little advice about css.

Using data.table j to fill a matrix (more generally to interact with outside variables)

r data.table

I am trying to benefit from data.table fast grouping to fill a matrix (or do other stuff externally from the data.table).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.