CODEWITHSUNDEEP
sqlduplicatesrow
anand
anand

Reputation: 13

mySQL query to find duplicate row

Exmaple:

[empid    date      bookid]
----------
1        5/6/2004   8

2        5/6/2004   8

1        5/7/2004   8

1        5/8/2004   6

3        5/8/2004   8

2        5/8/2004   7

In this table,I need to get empid 1 as output..since it has bookid 8 more than once..

thanks in advance..

Upvotes: 1

Views: 3142

Answers (2)

Alin P.
Alin P.

Reputation: 44346

You can use:

SELECT DISTINCT id
FROM table
GROUP BY empid, bookid
HAVING COUNT(*) > 1

But it will give you duplicates. If, for example, you have 1-8,1-8,1-9,1-9 you will get 1,1 as output because empid 1 has duplicate bookid's for two distinct bookid values. You will need to use SELECT DISTINCT to filter out the duplicate empid.

Upvotes: 4

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

SELECT empid
from table
group by empid
having Count(distinct bookid) > 1

Upvotes: 0

Related Questions