none
SQL Find DUplicates query

    Question

  • Hi all,

    i can find duplicate emails in my table like this

    select ExistingEmail, count(ExistingEmail) as cnt
    from YM_Email_Updates group by ExistingEmail
    having(Count(ExistingEmail)>2)

    but that groups and returns the email address and the NO of duplicates which is good.  Is there a way to modify this query to actually return the ENTIRE row (not just the one column)

    would it involve including this query as a subquery.  Ideally id like to return all the columns of all rows with duplicates but this query only returns the email address.  I have tried adding the other cols and including them in the grouping but it does not work .

    TIA,

    mcm

    Friday, February 02, 2007 7:18 PM

All replies

  • what are the other columns on your table and wht is the primary key on this table...can you also post some sample data...i can help you then
    Friday, February 02, 2007 7:35 PM
  • my table is simple

    it is

     

    dbid (PK Identity increment)
    EventDate (varchar)
    ExistingEmail (varchar)
    OldEmail (varchar) <- most are null about 5% has a value in here
    EventCode (vardchar)

    thats how it is. ideally id like to pull at the very least dbid: EventDate and Email

    thanks,

    mcm

    Friday, February 02, 2007 7:42 PM
  • Hi,

    Strange, but I expect that this one will work:

    select * from YM_Email_Updates group by ExistingEmail
    having(Count(ExistingEmail)>2)

    At least it is working in other DBMSs

    Thanx, Dimitry
    Friday, February 02, 2007 8:53 PM
  • unfortunately it did not. 

    i think i need to do a subquery to populate a temp field with the number of dupes, then just pull all records that match that

     

    my error is that the other columns are not grouped for the aggregate function.

    thanks,

    mcm

    Friday, February 02, 2007 9:21 PM
  • Try:

    select * from YM_Email_Updates,

    (select ExistingEmail, count(*) EmailCount from YM_Email_Updates group by ExistingEmail having count(*)>1) SS1

    where SS1.ExistingEmail = YM_Email_Updates.ExistingEmail

    Rich

    www.richreeves.com

     

    Sunday, February 04, 2007 9:52 PM