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

What about `HZ 1234`? Why is this row not returned in your example? — Joël Salamin, Sep 29 '14 at 14:06

vc 74 · Accepted Answer · 2014-09-29T14:30:02.290

8

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

edited Sep 29 '14 at 14:30

answered Sep 29 '14 at 14:08

vc 74

34,724
7
58
80

Be careful with name `table`, it's for the example but in this case use another name or use backticks. +1 for the solution – Joël Salamin Sep 29 '14 at 14:16
@JoëlSalamin Thanks for spotting this, I've updated the query – vc 74 Oct 20 '14 at 08:10

score 2 · Answer 2 · answered Sep 29 '14 at 14:11

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

answered Sep 29 '14 at 14:11

neshkeev

5,680
2
22
44

score 2 · Answer 3 · answered Sep 29 '14 at 14:18

2

If you need just zipcode, use vc 74's solution. For all columns , solution based on window functions supposedly outperforms self join approach:

SELECT a.zipcode, a.name 
FROM 
(
   SELECT zipcode, name, count(1) over(partition by zipcode) as cnt
   FROM your_table
)a
WHERE a.cnt >1

answered Sep 29 '14 at 14:18

a1ex07

35,290
12
78
96

Outperforms self join, but underperforms compared to simply using aggregate functions FIRST and LAST – Rob van Wijk Sep 29 '14 at 14:23

Find not unique rows in Oracle SQL

3 Answers3