List of duplicates SQL

Question

I have a table with the following columns:

ID
Link
price

There are some duplicates in column Link, how can I get a list of duplicates.

My query:

SELECT * 
FROM tbl
WHERE Link
IN
(SELECT Link
FROM tbl
GROUP BY Link
HAVING CONT(*) > 1;

It returns all the fields with duplicates, but I need to list them, so I can see all of them (fields with duplicates).

Add some sample table data and the expected result - as formatted text, not images. — jarlh, Sep 13 '18 at 09:31
Possible duplicate of [Find duplicate records in MySQL](https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) — Madhur Bhaiya, Sep 13 '18 at 09:45
What your query not gives you? you need one another column with count of duplicates? or what ? — Oto Shavadze, Sep 13 '18 at 09:46
it returns just a row with duplicates but i need to get all duplicates like: ID1 LINK1 PRISE1; ID2 LINK1 PRISE2; ID3 LINK3 PRISE3; ID4 LINK3 PRISE4; — Bohdan Semko, Sep 13 '18 at 09:50
I guess you just need to order your data by Link. just add `order by Link` to end of your query. — Serkan Arslan, Sep 13 '18 at 10:07

Marko Juvančič · Answer 1 · 2018-09-13T09:36:25.370

0

Try this:

  SELECT Link
    FROM tbl
GROUP BY Link
HAVING COUNT(*) > 1

edited Sep 13 '18 at 09:36

answered Sep 13 '18 at 09:32

Marko Juvančič

1

The sub-query alone will produce the same result. – jarlh Sep 13 '18 at 09:34

score 0 · Answer 2 · answered Sep 13 '18 at 09:36

0

You could just do:

SELECT Link, COUNT(*)
FROM tbl
GROUP BY Link
HAVING COUNT(*)>1

In MySQL, you can even leave out the COUNT(*) from the SELECT.

SELECT Link
FROM tbl
GROUP BY Link
HAVING COUNT(*)>1

answered Sep 13 '18 at 09:36

slaakso

it returns just a row with duplicates but i need to get all duplicates like : ID1 LINK1 PRISE1 ID2 LINK1 PRISE2 ID3 LINK3 PRISE3 ID4 LINK3 PRISE4 – Bohdan Semko Sep 13 '18 at 09:40
In that case the query in your question shout produce the result you want (you are just missing the closing parentheses). What is wrong with the output of your original query? – slaakso Sep 13 '18 at 09:49
It returns a result like: ID1 LINK1 PRISE1; ID3 LINK3 PRISE3; But not all list. – Bohdan Semko Sep 13 '18 at 09:54
And what would the "all list" look like? – slaakso Sep 13 '18 at 10:04
ID1 LINK1 PRISE1; ID2 LINK1 PRISE2; ID3 LINK3 PRISE3; ID4 LINK3 PRISE4; – Bohdan Semko Sep 13 '18 at 10:07
Your query will list all rows which have Link-column values that appear more than once in the data (="all list"). Please provide sample data, your query and the desired results. – slaakso Sep 13 '18 at 10:26

score 0 · Answer 3 · answered Sep 13 '18 at 10:12

If you want the full rows, then I would recommend:

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

This assumes that id is unique, which seems like a reasonable assumption.

3 Answers3