List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 20 1999 12:09am
Subject:Re: Finding duplicate records
View as plain text  
On Thu, 1999-08-19 11:03:27 +0200, Richard Ellerbrock wrote:
> >SELECT col, count(col) AS n FROM table1 GROUP BY col HAVING n > 1;
> 
> This scheme does not quite work. It only returns the first record of the duplicates.
> I want to return ALL records that are duplicate.
> 
> Assume I have a table as follows:
> 
> FieldA     FieldB
> ¯----------------
> abc        123
> abc        234
> abc        345
> xyz        999
> xyz        999
> jhk        777
> poi        888
> 
> Now I want to check for dups on FieldA. Your query returns 
> 
> abc         123
> xyz         999
> 
> I want to return the first 5 records! Bummer!
> 
> The other scheme proposed by Martin returns no records at all.

Well, it does here.  That's maybe a problem with older version of
MySQL where you can't write
   SELECT ... FROM ... HAVING COUNT(*)>0
but have to output and name this value, i.e.
   SELECT ..., COUNT(*) AS n FROM ... HAVING n>0


Now with your example, I understood what you really want.

Here's something that is very near to a solution: :-)
  SELECT t1.a, t1.b
  FROM   tmp AS t1
       , tmp AS t2
  WHERE t1.a = t2.a
  GROUP BY t1.a, t1.b
  HAVING COUNT(*)>1;

This will give you all distinct duplicates, but for example it will
miss the second (xyz, 999).

I can't think of a single MySQL query to solve your problem entirely
the way you intend ... (maybe it can be done, I don't know.)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Finding duplicate recordsRichard Ellerbrock18 Aug
  • Re: Finding duplicate recordsMartin Ramsch18 Aug
  • Re: Finding duplicate recordssasha18 Aug
  • Re: Finding duplicate recordsColin McKinnon18 Aug
Re: Finding duplicate recordsRichard Ellerbrock19 Aug
  • Re: Finding duplicate recordsMartin Ramsch20 Aug
    • Re: Finding duplicate recordsBenjamin Pflugmann20 Aug
Re: Finding duplicate recordsAndrey Muratov19 Aug
Re: Finding duplicate recordsAndrey Muratov19 Aug