Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle distinct

Posted on 2011-03-06
19
Medium Priority
?
824 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
Technology Partners: 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 2000 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

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!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

704 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