2

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

beej
  • 135
  • 1
  • 9
  • Do you have any unique column like an auto increment? If so, you can group by that instead of a,b. – Daniel Pereira Sep 02 '11 at 03:12
  • @Daniel: how are you going to get duplicates when you group on a column that only has unique values? – gview Sep 02 '11 at 18:11
  • duplicate http://stackoverflow.com/questions/7165330/expand-a-group-by-and-having-resultset – theking963 Sep 02 '11 at 20:23
  • If the duplicates are at the column level, then grouping by a unique column will show all the duplicates. It wasn't clear to me that we were talking about duplicate rows. – Daniel Pereira Sep 04 '11 at 21:05

6 Answers6

1

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

Sparky
  • 14,967
  • 2
  • 31
  • 45
1
SELECT a,b, 
 COUNT(a) AS A,
 COUNT(b) AS B
FROM tbl
GROUP BY a
HAVING A > 1;
ajreal
  • 46,720
  • 11
  • 89
  • 119
Daniel Pereira
  • 1,785
  • 12
  • 10
1

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
gview
  • 14,876
  • 3
  • 46
  • 51
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.

beej
  • 135
  • 1
  • 9
0

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.

Klemen Tusar
  • 9,261
  • 4
  • 31
  • 28
-2
SELECT a,b, COUNT(*) AS nmb FROM tb1 GROUP BY a,b HAVING nmb > 1;
ninjaaa
  • 293
  • 2
  • 11