List all dulicates

[fryguy5049]

Using MySQL 4
I am trying to query all duplicate occurences of an account # (acct)in my DB. This is the command I have so far:

SELECT cd71.amt ,cd71.acct ,cd71.date, store
from cd71
where cd71.acct=cd71.acct and cd71.date >'2004-09-20' and cd71.tran ='253' and cd71.amt >1000

group by cd71.acct having count(*)>1;

This almost works, but it only lists one instance on the duplicate. How can I get the command to list each row that is a duplicate? Thanks

 

[r937]

shot in the dark:

select t1.amt 
     , t1.acct 
     , t1.date
     , t1.store 
  from cd71 as t1
inner
  join cd71 as t2
    on t1.acct 
     = t2.acct  
 where t1.date > '2004-09-20' 
   and t1.tran = '253' 
   and t1.amt > 1000 
group 
    by t1.amt 
     , t1.acct 
     , t1.date
     , t1.store 
having count(*) > 1;

rudy

http://r937.com/

SQL Consulting

 

[fryguy5049]

Didn't seem to work. That command returned 288 rows where is my original command returned 2 (should have been 4 rows). Not too sure what happenend but thanks for the effort.

 

[ericbrunson]

If you are using a server version that supporst subselects (> 4.1?) you can use something like this:

SELECT amt, acct, date, store
from cd71
where acct in (
 select acct 
 from cd71
 where date >'2004-09-20' 
 and tran ='253' 
 and amt >1000
 group by acct having count(*)>1 );