Find unique emai id record in sql?

Question

Empid   Deptid  Email
39  42  uni@mail.com
38  43  uni@mail.com
37  44  uni@mail.com
25  35  unique@maill.com
26  36  second@maill.com

How to find only unique record ? empid -->25 and 26 .

I tryed below query.

select empid,deptid,row_number() over(PARTITION BY lower(email) Order By empid desc) from emp 
group by email,empid

Possible duplicate of [SQL/mysql - Select distinct/UNIQUE but return all columns?](https://stackoverflow.com/questions/6127338/sql-mysql-select-distinct-unique-but-return-all-columns) — Khan M, Feb 09 '18 at 14:51

Wernfried Domscheit · Accepted Answer · 2018-02-09T07:50:58.943

3

What about this:

select *
from emp
where Email =any (select Email from emp group by Email having count(*) = 1);

I also found a version without sub-query or self-join. At least from theory it should have the best performance from all.

SELECT 
    MAX(Empid) KEEP (DENSE_RANK FIRST ORDER BY NULL) AS Empid, 
    MAX(Deptid) KEEP (DENSE_RANK FIRST ORDER BY NULL) AS Deptid,
    Email
FROM emp
GROUP BY Email
HAVING COUNT(*) = 1;

edited Feb 09 '18 at 07:50

answered Feb 09 '18 at 07:13

Wernfried Domscheit

54,457
9
76
110

Neat. I was going to suggest the `keep (dense_rank first)` approach but you've saved me the trouble. I think this construction gets overlooked as the syntax is a bit verbose compared to a nested analytic `count`, but it's nice to do the whole thing in a single `group by`. – William Robertson Feb 09 '18 at 10:01

score 3 · Answer 2 · answered Feb 09 '18 at 07:21

You may use COUNT analytical function.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE emp 
    (Empid int, Deptid int, Email varchar2(16))
;

INSERT ALL 
    INTO emp  (Empid, Deptid, Email)
         VALUES (39, 42, 'uni@mail.com')
    INTO emp  (Empid, Deptid, Email)
         VALUES (38, 43, 'uni@mail.com')
    INTO emp  (Empid, Deptid, Email)
         VALUES (37, 44, 'uni@mail.com')
    INTO emp  (Empid, Deptid, Email)
         VALUES (25, 35, 'unique@maill.com')
    INTO emp  (Empid, Deptid, Email)
         VALUES (26, 36, 'second@maill.com')
SELECT * FROM dual
;

Query 1:

SELECT Empid, Deptid, Email
  FROM (SELECT e.*, COUNT (Empid) OVER (PARTITION BY Email) ct
          FROM emp e)
 WHERE ct = 1

Results:

| EMPID | DEPTID |            EMAIL |
|-------|--------|------------------|
|    26 |     36 | second@maill.com |
|    25 |     35 | unique@maill.com |

score 1 · Answer 3 · answered Feb 09 '18 at 07:10

You can use a not exists query to do that:

select e1.*
from emp e1
where not exists (select *  
                  from emp e2
                  where e2.empid <> e1.empid
                    and lower(e2.email) = lower(e1.email));

score 1 · Answer 4 · answered Feb 09 '18 at 07:13

1

Another option:

select empid
from emp
where email in (select email 
                from emp
                group by email
                having count(*) = 1);

answered Feb 09 '18 at 07:13

Littlefoot

131,892
15
35
57

score 0 · Answer 5 · edited Feb 09 '18 at 07:43

0

SELECT *
    FROM emp
    WHERE email IN (SELECT email FROM emp GROUP BY email HAVING COUNT(*)=1)

edited Feb 09 '18 at 07:43

Kos

4,890
9
38
42

answered Feb 09 '18 at 07:32

Seyedhamed Vahedi

11
1

Find unique emai id record in sql?

5 Answers5