Make a query to find duplicates

[ASHER]

I'm working in SQL with PhpMyAdmin. I have a big table. By doing something like:

SELECT DISTINCT upper(last_name), upper(first_name)  FROM visitors 

I found a beautiful table with no duplicates. Indeed, using upper (or lower), take off (well I think) a problem of breakage, which allows me to really remove all duplicates. However, I would like to make a query that gives me all the fields in double and not the fields that are not duplicated. I try to have that basis for me to working on it and clean, manually. I admit not having too many ideas ...

[CitricAcid]

select upper (last_name), upper (first_name), count (identifier) 
from thetable 
group by upper (last_name), upper (first_name) 
having count (identifier)> 1 

[ASHER]

This request is not bad at all. Was thinking, I could not have. But it is not exactly what I need. In fact, it tells me for each record the same registration number (2,3,4 ,...).

Example,
Salman Khan - 3
Amir Khan - 2
Sameer Khan - 7 ;-)

I have all the information for these duplicates. (as society, the city ,...). For example, to see the 3 recordings of Salman Khan, so I can see which is correct, and which I can delete. And this, according to factors in addition, as the city and society.

Not much, but here's the basic code received by CitricAcid(thank you to him) with city and company ... Maybe it'll help a little to help

select upper(last_name), upper(first_name), society, city, count(id) from thetable group by upper(last_name), upper(first_name) having count(id) > 1 

[CitricAcid]

You can always add company and city in the GROUP BY clause to refine the results (in the first post, you do not speak that name). Otherwise, it's up to you from the list of duplicates (full name) and then perform the processing element by element ...

[Katty]

It depends what you call "element": ie how you define the uniqueness in your table, there is no rule on the matter: as it was said at the beginning you do not speak in name / first name, then you talk about name / first name / company / city. This is not the same thing.

To shield the stuff in the future, I advise you to perform a check on the uniqueness before insertion, but, I guess you've checked out the stuff as is.