CODEWITHSUNDEEP
sqldatabasepostgresql
Flavio de Falcao e Helena
Flavio de Falcao e Helena

Reputation: 65

POSTGRESQL Selecting only duplicate accounts that have at least one duplicate phone number

I would like to get this sample data (POSTGRESQL):

uuid       email       phone_number        
    1   [email protected]        111
    2   [email protected]        111
    3   [email protected]        112
    4   [email protected]        222
    5   [email protected]        222
    6   [email protected]        333
    7   [email protected]        444
    8   [email protected]        445
    9   [email protected]        446

And eliminate all the:

  1. non duplicate email entries (in the example the uuid = 6)
  2. rows with duplicate emails but all the phone numbers are different (in the example, uuid = 7,8,9)

And mantain:

  1. one of the rows with duplicate email entries and all phone numbers equal (in the example, uuid = 4,5)
  2. one of the rows with duplicate email entries and at least two phone numbers equal (in the example, uuid = 1,2,3)

The result data would be

      email       phone_number        
   [email protected]        111
   [email protected]        222

Upvotes: 0

Views: 145

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

From what you describe, you want:

select email, max(phone_number)
from t
group by email
having count(*) > count(distinct phone_number) and
       count(*) > 1;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use group by clause with having 

select email, phone_number 
from table t
group by email, phone_number  
having count(*) > 1;

Upvotes: 1

Related Questions