List duplicate names in Mysql for change

Asked

Viewed 2,115 times

1

I need to list all the duplicate names in a certain table and I’m not getting it, what I have so far is this:

SELECT nome, foto, ativo, count(nome) FROM comColaborador GROUP BY nome HAVING count( nome ) > 1

But this script only shows me a record and its count indicating duplicity, I need to list the same.

I tried that too:

SELECT nome, foto, ativo FROM comColaborador WHERE nome LIKE "%ABRAAO BRANDAO MORAES%"

But it gets too expensive to have to search by stating the names I wish.

mysql

1 answer

1


Try doing with a subquery like this:

SELECT nome, foto, ativo FROM comColaborador 
WHERE id in(
    SELECT id FROM comColaborador GROUP BY nome HAVING count( nome ) > 1
)

I didn’t have time to test it here but I think it’s right

  • I did a test, but did not take duplicates, but thanks for the help @Wagner Soares.

  • Strange, it was supposed to work out kkkkk

  • Hi @Wagner Soares, is this "id" from my chart? Looks like this: SELECT name, photo, active FROM comColaborator WHERE idColaborator in ( SELECT idColaborator FROM comColaborator GROUP BY name HAVING Count( name ) > 1 order by name asc )

  • This didn’t work out that way?

  • Just one observation, to sort the final result order by must be after the ")" like this: SELECT name, photo, active FROM comColaborator WHERE IDCOLABORATOR in ( SELECT idColaborator FROM comColaborator GROUP BY name HAVING Count( name ) > 1 ) order by name asc

Browser other questions tagged mysql

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