Solved

Oracle distinct

Posted on 2011-03-06
19
794 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
  • 8
  • 5
  • 3
  • +2
19 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
Comment Utility
use union in place of union all then tell us what is your output
0
 
LVL 4

Expert Comment

by:pinkuray
Comment Utility
also you can use what pratima_mcs: suggested
0
 

Author Comment

by:GRChandrashekar
Comment Utility
@pratima_mcs:

No change in output
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
Comment Utility
@ pratima_mcs:

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

Expert Comment

by:Pratima Pharande
Comment Utility
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
Comment Utility
Error
ORDER BY  X.MEMBERNAME
NOT A GROUP BY EXPRESSION
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:GRChandrashekar
Comment Utility
No error but output is still the same

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

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the dā€¦
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  ā€¦
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

9 Experts available now in Live!

Get 1:1 Help Now