Remove duplicates based om value of one field

Posted on 2011-10-28
Last Modified: 2012-05-12

When I do the following I will get a list of Names and their Emailaddress.

Now I want to make sure that the same E-mailaddress is never more than once in the selection.
Is there a way to filter these out somehow?
Question by:Delphiwizard
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Not sure I understand.  What it the email is the same but name is different.

    What do you want returned?

    Sample data and expected results would help.
    LVL 76

    Accepted Solution

    To just grab the first email if they repeat try this:

    select name, email from
    select name, email, row_number() over(partition by email order by email) as myrn
    from table
    where myrn=1;

    Author Closing Comment

    This is what I intended. Thank you.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now