Get Duplicate Data

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  August 25, 2005 at 10:00 pm

  #94709

  I have the following query to get the duplicate AccountNames;

  SELECT AccountName

  FROM Account

  GROUP BY AccountName

  HAVING COUNT(*) > 1

  How do I also retrieve the AccountID as well as its duplicate AccountName as well?

  Example;

  AccountID        AccountName

  1                     ABC

  2                     ABC

  3                     XYZ

  4                     XYZ

  ---

  Kindest Regards,

• wz700

  SSCertifiable

  Points: 7133

  More actions

  August 25, 2005 at 11:04 pm

  #585279

  Try:

  select a.AccountID, a.AccountName, b.Duplicated

  From Account a

  inner join (

  select accountname, count(*) as duplicated

  from account group by accountname

  having count(*) > 1) b on a.accountname = b.accountname

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  August 26, 2005 at 12:39 am

  #585292

  Thanks wz700. Is the JOIN what's called a Derived Table?

  ---

  Kindest Regards,