CODEWITHSUNDEEP
sqloracle-database
Mikayil Abdullayev
Mikayil Abdullayev

Reputation: 12376

How to select persons which have unique full name

There was no way I could fit what I need in the title, so sorry for that. Anyway, here I have a table:

CREATE TABLE DOCUMENTS 
(
  ID NUMBER (5,0)
, PIN VARCHAR2(7 BYTE) 
, FIRST_NAME VARCHAR2(20 BYTE) 
, LAST_NAME VARCHAR2(20 BYTE) 
);

INSERT INTO "JM_WARRANT"."DOCUMENTS"  VALUES (1,'AZU5FG', 'JOHN', 'SMITH')
INSERT INTO "JM_WARRANT"."DOCUMENTS"  VALUES (2,'BG45SF', 'MARY', 'BROWN')
INSERT INTO "JM_WARRANT"."DOCUMENTS"  VALUES (3,'AZU5FG', 'JOHN', 'SMITH')
INSERT INTO "JM_WARRANT"."DOCUMENTS"  VALUES (4,'WHT56B', 'JOHN', 'SMITH')
INSERT INTO "JM_WARRANT"."DOCUMENTS"  VALUES (5,'BG45SF', 'MARY', 'BROWN')
INSERT INTO "JM_WARRANT"."DOCUMENTS"  VALUES (6,'ZSF09V', 'JOSCH','ZEPLIN')

 ID   PIN    FIRST_NAME LAST_NAME
---|--------|----------|-------
1  |AZU5FG  | JOHN     | SMITH
2  |BG45SF  | MARY     | BROWN
3  |AZU5FG  | JOHN     | SMITH
4  |WHT56B  | JOHN     | SMITH
5  |BG45SF  | MARY     | BROWN
6  |ZSF09V  | JOSCH    | ZEPLIN

Simply put a person is uniquely identified by PIN. As you know different people can have the same name. From this table I need to select only those ones that don't share the same name with anyone else.Note that one person can have more than one records in the table. So if we take this sample data table, MARY BROWN and JOSCH ZEPLIN are eligible, because they have unique names.Although MARY BROWN has two records, it's the same person because of the same PIN. But JOHN SMITH doesn't meet the requirement because there are two persons with the same name. What would be the correct SQL to get this result?

EDIT: Also, I need to get only one record. So basically from this table I would need to see the below result:

   PIN    FIRST_NAME LAST_NAME
--------|----------|-------
 BG45SF  | MARY     | BROWN
 ZSF09V  | JOSCH    | ZEPLIN

Upvotes: 0

Views: 86

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Use not exists to filter such persons:

select distinct FIRST_NAME, LAST_NAME, PIN 
from DOCUMENTS d1
where not exists(select * from DOCUMENTS d2 
                 where d1.FIRST_NAME = d2.FIRST_NAME and 
                       d1.LAST_NAME = d2.LAST_NAME and 
                       d1.PIN <> d2.PIN )

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Group by first and last name and count distinct pins:

select first_name, last_name, min(pin) as pin
from documents
group by first_name, last_name
having count(distinct pin) = 1;

Upvotes: 3

Related Questions