MySQL Show Duplicates

I am currently using a version of:

SELECT a,b
FROM tbl
GROUP BY a,b
HAVING COUNT(*)>1;

I know the results are correct given what ac开发者_开发技巧tions I've taken on the table. But, it doesn't actually show me the duplicates. The "GROUP BY" clause blocks the other half of the records from showing.

I know this probably has a simple solution, but how do I actually show the duplicates? In other words, if there are 110 duplicates, I should get 220 records.

Thanks

Try this

select * 
from tbl DP
join
(
SELECT a,b
FROM tbl
GROUP BY a,b
HAVING COUNT(*)>1;
)  xx
where xx.a=DP.a and xx.b=DP.B

Not exactly sure of mySQL syntax, but this SQL should work

I'm assuming that what you meant is that tbl has more columns than a,b and possible a pk id.

SELECT t1.*
FROM tbl t1
JOIN (
SELECT a, b
FROM tbl
GROUP BY a, b
HAVING COUNT(*) > 1
) t2
ON t1.a = t2.a AND t1.b = t2.b

SELECT a,b, 
 COUNT(a) AS A,
 COUNT(b) AS B
FROM tbl
GROUP BY a
HAVING A > 1;

I apologize. Since I knew the limits for field b, I was really using the wrong kind of statement. I ended up going with:

SELECT * FROM tbl
    WHERE (b = x OR b = y)
    AND a IN (SELECT a FROM tbl WHERE b = y)
    ORDER BY a ASC

This gave me exactly what I needed.

Thanks for all the input.

I had very much success with this:

SELECT DISTINCT
    tbl.user_id,
    tbl.duped_value
FROM your_table AS tbl
WHERE EXISTS (
    SELECT COUNT( tbl2.duped_value ) AS dcnt
    FROM your_table AS tbl2
    WHERE tbl.duped_value = tbl2.duped_value
    GROUP BY tbl2.user_id
    HAVING dcnt > 1
)

What it does is it searches for entries that have a duplicate distinct value, like say a video ID from YouTube or something similar which should be unique per a user's entry.

SELECT a,b, COUNT(*) AS nmb FROM tb1 GROUP BY a,b HAVING nmb > 1;