Check for dupes in db with TWO rows

Question

I´d like to check for dupes in MySQL comparing two columns:

Linked duplicate doesn't match as it's about finding the duplicate values on specific columns. This question is about finding the rows which have said duplicate values.

Example:

id    Column1    Column2
-------------------------
       3          1     <-This row is a dupe
       3          2
       3          3
       3          1     <-This row is a dupe

I'd like to have a list like this:

id    Column1    Column2
-------------------------
        3        1
        3        1

How should this query look like?

My thinking:

SELECT * FROM table
WHERE Column1 && Column2 is a dupe ;)

possible duplicate of [Select statement to find duplicates on certain fields](http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields) — Trevi Awater, Sep 30 '15 at 08:29
Linked duplicate doesn't match as it's about *finding the duplicate values* on specific columns. This question is about *finding the rows* which have said duplicate values. — vhu, Sep 30 '15 at 08:47

score 2 · Accepted Answer · answered Sep 30 '15 at 08:32

2

You can use this to find the duplicates:

SELECT column1, column2
 FROM yourtable
 GROUP BY column1, column2
 HAVING count(*) > 1;

And to show the actual duplicate rows you can JOIN against results of the above query:

SELECT * FROM yourtable yt1
 JOIN (SELECT column1, column2
 FROM yourtable
 GROUP BY column1, column2
 HAVING count(*) > 1) yt2 ON (yt2.column1=yt1.column1 AND yt2.column2=yt1.column2);

answered Sep 30 '15 at 08:32

vhu

12,244
11
38
48

What is yt1 and yt2? "Yourtable 1" etc? – Björn C Sep 30 '15 at 08:38
@Mjukis : yes its yourtable 1 and yourtable2 – naggarwal11 Sep 30 '15 at 08:40
This works perfectly. Thank you. I did not change "yt1" or "yt2". What do you do, when you use it after "yourtable"? Can i read up on this? – Björn C Sep 30 '15 at 08:42
No these are simply alias to your table names . You can use them as it is. – naggarwal11 Sep 30 '15 at 08:43
As @naggarwal11 correctly pointed out, `yt1` and `yt2` are aliases (abbreviations). `yt1` is just a alias for `yourtable` while `yt2` is alias for result set of the subquery. – vhu Sep 30 '15 at 08:49
Well, when i look at the result now i may have bin a little rushy. Some result i do not understand why they came up. I used: "FROM mytable yt1..." Should i remove "yt1.column2" to "mytable.column2". But where does yt2 come from? i have only 1 table.. with two columns.. i want to check.. – Björn C Sep 30 '15 at 08:51
You mean they are "placeholders"? When i write: "mytable yt1" i put "mytable" into "yt1"? – Björn C Sep 30 '15 at 08:53
1

If you change `yourtable` into `mytable` you don't need to change `yt1` or `yt2`. It is correct that you have only one table, but you may consider `yt2` as a temporary table that exists only during this query. – vhu Sep 30 '15 at 08:53
2

Yes, you might call them placeholders. I prefer to call them aliases as that's also used in the [documentation](https://dev.mysql.com/doc/refman/5.1/en/join.html) – vhu Sep 30 '15 at 08:55
@vhu I tried to add "ORDER BY" whitout no succes. I get a query error. Where can i add this prop? – Björn C Sep 30 '15 at 09:25
2

reminder that you have a link to the documentation right above – underscore_d Sep 30 '15 at 09:33

chris · Answer 2 · 2015-10-06T07:25:19.677

0

select * , count(column1)
from table
group by column1, column2
having (count(column1) >1)

that is one solution. you will get all columns which exist more than one time, through the having. and the group by collects all together. the count could also go on both or column 2, that is not so important

edited Oct 06 '15 at 07:25

answered Sep 30 '15 at 08:24

chris

125
13

With this code, i only get ONE row from each dupplicate. I need to se each two(or more) rows! – Björn C Sep 30 '15 at 08:29
select * , count(column1) from table group by column1, column2 having (count(column1) >1) – chris Sep 30 '15 at 08:31
oh sorry my fault `select * , count(column1, column1) from table having (count(column1) >1)` i think this should help – chris Sep 30 '15 at 08:31

score 0 · Answer 3 · answered Sep 30 '15 at 08:31

0

You can try this. This will provide you all the duplicate entries

select column1,column2 from table_name group by column1,column2 having count(column1)>1

answered Sep 30 '15 at 08:31

naggarwal11

122
1
14

@naggarwall11 This code will only give me one of each dupes. Not ALL the dupes entries. – Björn C Sep 30 '15 at 08:36

Check for dupes in db with TWO rows

3 Answers3