Search Duplicity in the table

Asked

Viewed 95 times

5

I have a chart with 3 million phones, and I have the code for each customer. My goal is to find which phone repeats on different customers.

For that, I can count how many times he repeats, but I can’t know to whom he repeats.

Follow an example of the code.

SELECT      PARCEIRO
        ,   TELEFONE
        ,   COUNT(TELEFONE) AS QTD
FROM        tabela
GROUP BY    TELEFONE
        ,   PARCEIRO
HAVING      COUNT(*)>1
ORDER BY    COUNT(PARCEIRO)
mysql

  • FROM tabela; that ; closes before grouping.. that’s not the problem?

  • Ah disregard ;, typo hehe.

  • However if I turn the code, it gives me the right amount , however legal I have the amount of repetitions but what I want and know to whom it repeats.

  • I corrected the accent, and I removed all the tags. If it is not Mysql, put ONLY the tag of your database, because it is a specific case, and not general.

2 answers

6

You can do Join using the same table with different aliases, follow the example:

SELECT t1.PARCEIRO, t1.TELEFONE FROM tabela as t1
JOIN tabela as t2
    /* mesmo telefone */
    ON t1.TELEFONE = T2.TELEFONE 
    /* parceiro diferente */
    and t1.PARCEIRO <> t2.PARCEIRO /* and t1.PARCEIRO != t2.PARCEIRO */
ORDER BY t1.PARCEIRO, t1.TELEFONE

3

Look, you don’t need to apply any type of Jay. This one is simpler and I even think more performant yet. And I think it would still work:

SELECT      COUNT(*) AS QTD,PARCEIRO,TELEFONE FROM tabela
GROUP BY    PARCEIRO, TELEFONE
HAVING      COUNT(*)>1
ORDER BY    QTD

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.