[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Remove duplicates based om value of one field

Posted on 2011-10-28
3
Medium Priority
?
224 Views
Last Modified: 2012-05-12
Hi,

When I do the following I will get a list of Names and their Emailaddress.
SELECT DISTINCT Name, Email

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?
0
Comment
Question by:Stef Merlijn
  • 2
3 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044849
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.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37044862
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;
0
 

Author Closing Comment

by:Stef Merlijn
ID: 37044958
This is what I intended. Thank you.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

834 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