Avatar of hoyaabanks

asked on 

Selecting Duplicate Emails in a single table

I am trying to select duplicate email address by using the query below.  You see I am selecting tehm into a table where I can manipulate them.

However this query isn't working.  And I really need to trim extra emails address.

Example: If there are 5 records with the same email address, I want to select and remove the 4 records with the same email address that were entered after the 1st.

    devUserProfile.eMail, devIssuedCert.* into #DuplicateEmails
FROM         devIssuedCert LEFT OUTER JOIN
                      devUserProfile ON devIssuedCert.UserProfile_ID = devUserProfile.UserProfile_ID
    (COUNT(devUserProfile.eMail) > 1)

SELECT * FROM #DuplicateEmails

DROP TABLE #DuplicateEmails

Returns Error:

Msg 8120, Level 16, State 1, Line 1
Column 'devUserProfile.UserProfile_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment

8/22/2022 - Mon