CODEWITHSUNDEEP
mysql
user1048337
user1048337

Reputation:

How to identify and select all duplicated entries

I have trying to identify and select all dublicated entries filled under an row in my mysql table.

I have tried using this query:

SELECT id, link, COUNT(*) 
FROM linkuri
HAVING COUNT(*)>1
LIMIT 0 , 30

The problem is that is resulting me 0 results and I've checked manualy few pages and I've seen some dublicates entries.

What I want is to check and delete all dublicated entryes filled under the row link.

Upvotes: 2

Views: 77

Answers (2)

ILovePaperTowels
ILovePaperTowels

Reputation: 1455

The problem with your query is you're not grouping by anything. To find duplicates you have to group them by a column(s) and then get the count from each group. Having statement is like a where clause on the group http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

SELECT id, link, COUNT(*) 
FROM linkuri
GROUP BY link
HAVING COUNT(*)>1
LIMIT 0 , 30

Upvotes: 1

a1ex07
a1ex07

Reputation: 37382

You are probably looking for

SELECT a.id, a.link, b.cnt
FROM linkuri
INNER JOIN 
( SELECT link, COUNT(*) AS cnt FROM linkuri GROUP BY link HAVING COUNT(*) >1 )b
ON (b.link = a.link)

Upvotes: 1

Related Questions