MySQL - Select all rows where a field value is not unique

Question

How can I select all rows in MySQL where a particular field value is not unique. For example I have the following data:

---------------------------------------
| ID | Name   |         URL           |
---------------------------------------
| 1  | Store 1| http://www.store1.com |
| 2  | Store 2| http://www.store1.com |
| 3  | Store 3| http://www.store3.com |
| 4  | Store 4| http://www.store4.com |
| 5  | Store 5| http://www.store4.com |
---------------------------------------

In this I would want to return the following where the URL field has duplicates:

---------------------------------------
| ID | Name   |         URL           |
---------------------------------------
| 1  | Store 1| http://www.store1.com |
| 2  | Store 2| http://www.store1.com |
| 4  | Store 4| http://www.store4.com |
| 5  | Store 5| http://www.store4.com |
---------------------------------------

Strawberry · Accepted Answer · 2015-09-08 13:40:22Z

15

or, old school...

SELECT DISTINCT x.* 
           FROM my_table x 
           JOIN my_table y 
             ON y.url = x.url 
            AND y.id <> x.id 
          ORDER 
             BY id;

answered Sep 8, 2015 at 13:40

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AdrianBR Over a year ago

Love it! Missed the disctinct at first and was thinking you get more rows, but nope, it's perfect :)

AdrianBR Over a year ago

any idea how the performance of that is against the EXISTS solution? I can guess my sub-select solution would be the slowest

Wildcard Over a year ago

Love this old school style, making me think! :D

Gordon Linoff · Accepted Answer · 2015-09-08 13:34:09Z

7

If you want all the original rows, then use exists:

select t.*
from table t
where exists (select 1 from table t2 where t2.url = t.url and t2.id <> t.id);

answered Sep 8, 2015 at 13:34

Gordon Linoff

1.3m62 gold badges708 silver badges858 bronze badges

Comments

AdrianBR · Accepted Answer · 2015-09-08 13:39:01Z

1

You can inner join to your duplicates.

select t.* 
from table t
inner join
(select url from table group by 1 having count(*)>1) duplicates
on duplicates.url=t.url

answered Sep 8, 2015 at 13:39

AdrianBR

2,5781 gold badge17 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL - Select all rows where a field value is not unique

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related