Find not unique rows in Oracle SQL

Question

I have a question which looks easy but I can't figure it out.

I have the following:

   Name Zipcode

    ER 5354
    OL 1234
    AS 1234
    BH 3453
    BH 3453
    HZ 1234

I want to find those rows where the ID does not define clearly one row.

So here I want to see:

   OL 1234
   AS 1234
   HZ 1234

Or simply the zipcode enough.

I am sorry I forget to mention an important part. If the name is the same its not a problem, only if there are different names for the same zipcode. So this means: BH 3453 does not return

Answer 1

I think this is what you want

select zipcode
from yourTable
group by zipcode
having count(*) > 1

It selects the zipcodes associated to more than one record

to answer your updated question:

select zipcode
from
(
  select name, zipcode
  from yourTable
  group by name, zipcode
)
group by zipcode
having count(*) > 1

should do it. It might not be optimal in terms of performance in which case you could use window functions as suggested by @a1ex07

Answer 2

Try this:

select yt.*
  from YOUR_TABLE yt
     , (select zipcode
          from YOUR_TABLE
         group by zipcode
        having count(*) > 1
       ) m
 where yt.zipcode = m.zipcode