mySQL query to find duplicate row..

Question

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..

Answer 1

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

Answer 2

Hello,

You can use:

SELECT 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. I'm not sure if you can use SELECT DISTINCT to filter out the duplicates, but you can try.

Answer 3

Alin purcaru, Thanks for your ans..query works correct if i add distinct..

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