troubleshooting Question

Oracle distinct

Avatar of GRChandrashekar
GRChandrashekarFlag for India asked on
Oracle Database
19 Comments1 Solution893 ViewsLast Modified:
I have a query as follows

/* Formatted on 07/03/2011 11:41:34 AM (QP5 v5.136.908.31019) */
SELECT DISTINCT MEMBERNAME,EMAIL  FROM (SELECT MEMBER.MEMBERNAME, MEMBERADDR.EMAIL1 AS EMAIL
  FROM MEMBER, MEMBERADDR
 WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID) AND (MEMBER.FLAG = 0 AND MEMBERADDR.EMAIL1 IS NOT NULL)
 UNION ALL
 /* Formatted on 07/03/2011 11:41:34 AM (QP5 v5.136.908.31019) */
SELECT MEMBER.MEMBERNAME, MEMBERADDR.EMAIL2 AS EMAIL
  FROM MEMBER, MEMBERADDR
 WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID) AND (MEMBER.FLAG = 0 AND MEMBERADDR.EMAIL2 IS NOT NULL)) MEMBER
 ORDER BY  MEMBERNAME

OUTPUT

AMARNATH KAMATH      amarnathkamath@gmail.com
BHARATHI KAMATH       amarnathkamath@gmail.com

This is correct as per DB and query.

But I dont want duplicate email ID in ouput no matter which record it takes. I mean i need distinct on email ID no matter which name it takes.

I mean i want output like
AMARNATH KAMATH      amarnathkamath@gmail.com

second one is not required since email is duplicate though name is different
ASKER CERTIFIED SOLUTION
Pratima Pharande

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros