Querying for rowsets with a duplicate column value

• Robert Mabry

  SSC Journeyman

  Points: 90

  More actions

  April 21, 2005 at 10:00 am

  #362412

  How do I return all rowsets in a table that contain a duplicate value within a column?

  Example:

  OrderNumber         EmailAddresss

  00001                  joe_schmo@company.com

  00002                  mr_big@company.com

  00003                  joe_schmo@company.com

  Query should return the rowsets for OrderNumber 00001 and 00003.  Essentially, I need the opposite of DISTINCT.  Thoughts.

• Express12

  SSCoach

  Points: 18526

  More actions

  April 21, 2005 at 10:23 am

  #554140

  select OrderNumber, EmailAddress, count(EmailAddress)

   from your_table

    group by OrderNumber, EmailAddress

     having count(EmailAddress) > 1

  BT

• Robert Mabry

  SSC Journeyman

  Points: 90

  More actions

  April 21, 2005 at 12:32 pm

  #554186

  that'll work.  thanks bill.

• ron k

  SSCertifiable

  Points: 6103

  More actions

  April 21, 2005 at 2:28 pm

  #554218

  I guess I just don't understand because I don't see how the statement above could ever return any rows since grouping by OrderNumber, EmailAddress would never have a count > 1.  Aren't the OrderNumbers unique?

  Here's my solution:

  select distinct t1.OrderNumber, t1.EmailAddress

  from your_table t1

  join your_table t2

    on t1.EmailAddress = t2.EmailAddress

   And t1.OrderNumber <> t2.OrderNumber