Solved

Oracle distinct

Posted on 2011-03-06
19
806 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GRChandrashekar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
  • +2
19 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35053651
try this

Select distinct  X.MEMBERNAME,X.EMAIL
From (
/* 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 ) X
 ORDER BY  X.MEMBERNAME
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 35053660
use union in place of union all then tell us what is your output
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 35053672
also you can use what pratima_mcs: suggested
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GRChandrashekar
ID: 35053711
@pratima_mcs:

No change in output
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35053730
Select min (X.MEMBERNAME ),X.EMAIL
From (
/* 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 ) X
gorup by X.EMAIL
 ORDER BY  X.MEMBERNAME
0
 

Author Comment

by:GRChandrashekar
ID: 35053736
@ pratima_mcs:

Error gorup by X.EMAIL
SQL command not properly ended
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35053743
Select min (X.MEMBERNAME ),X.EMAIL
From (
/* 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 ) X
group by X.EMAIL
 ORDER BY  X.MEMBERNAME
0
 

Author Comment

by:GRChandrashekar
ID: 35053745
Error
ORDER BY  X.MEMBERNAME
NOT A GROUP BY EXPRESSION
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35053749
try only this

Select min (X.MEMBERNAME ),X.EMAIL
From (
/* 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 ) X
group by X.EMAIL
0
 

Author Comment

by:GRChandrashekar
ID: 35053768
No error but output is still the same

AMARNATH KAMATH      amarnathkamath@gmail.com
BHARATHI KAMATH      amarnathkamath@gmail.com
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35053854
Do you have extra spaces before/after EMAIL? Try with TRIM function.
SELECT MIN(X.MEMBERNAME), 
         TRIM(X.EMAIL) AS EMAIL 
    FROM (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 
                  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) X 
GROUP BY TRIM(X.EMAIL)

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35053856
actully this need to work..is there any spcae in meail
try this


Select min (X.MEMBERNAME ),trim(both ' ' from X.EMAIL) as EMAIL
From (
/* 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 ) X
group by trim(both ' ' from X.EMAIL)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35053876
In fact I suggest UNION  instead of UNION ALL.
SELECT MIN(X.MEMBERNAME), 
         TRIM(X.EMAIL) AS 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 
          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)) X 
GROUP BY TRIM(X.EMAIL)

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 35053901
No luck so far in any of the queries

AMARNATH KAMATH      amarnathkamath@gmail.com
BHARATHI KAMATH      Amarnathkamath@Gmail.Com
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 35053909
SELECT MIN(X.MEMBERNAME),
         X.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
          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)) X
GROUP BY UPPER(X.EMAIL)
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35053912
SELECT MIN(X.MEMBERNAME),
         UPPER(X.EMAIL) as 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
          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)) X
GROUP BY UPPER(X.EMAIL)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35053924
try this
SELECT MIN(X.MEMBERNAME), 
         X.EMAIL AS EMAIL 
    FROM (SELECT MEMBER.MEMBERNAME, 
                 TRIM(LOWER(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 
          SELECT MEMBER.MEMBERNAME, 
                 TRIM(LOWER(MEMBERADDR.EMAIL2)) AS EMAIL 
            FROM MEMBER, 
                 MEMBERADDR 
           WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID) 
                 AND (MEMBER.FLAG = 0 
                      AND MEMBERADDR.EMAIL2 IS NOT NULL)) X 
GROUP BY X.EMAIL

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35053934
SELECT MIN(X.MEMBERNAME),
         Lower(X.EMAIL) as 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
          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)) X
GROUP BY Lower(X.EMAIL)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35056099
I realize this is already closed but why have the union/joins/??? at all?

SELECT MEMBER.MEMBERNAME, nvl(MEMBERADDR.EMAIL1,MEMBERADDR.EMAIL2) AS EMAIL
  FROM MEMBER, MEMBERADDR
 WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID) AND (MEMBER.FLAG = 0 AND (MEMBERADDR.EMAIL1 IS NOT NULL or MEMBERADDR.EMAIL2 IS NOT NULL)))
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

756 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