Solved

Oracle distinct

Posted on 2011-03-06
19
1 solution
815 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
Join The Community
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Get NFR key
 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Reserve Your Spot
Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC
Article by: sdstuber
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…
Five Myths of Oracle Database Backup
Article by: Acronis
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Query Current Session Information via SQL
Video by: Steve
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Restoring a Database from User Managed Backups with Oracle
Video by: Steve
This video shows how to recover a database from a user managed backup
Advertise Here
Suggested Courses

623 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

Advertise Here