Post reply

Querying for rowsets with a duplicate column value

  • 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.

  • 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

  • April 21, 2005 at 12:32 pm

    #554186

    that'll work.  thanks bill.

  • 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

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply