[SQL]Select duplicate row and showing your ID and occurrences

With this post show you how select duplicate rows with same Country and City and relative ID.

Source Table to evaluate

ID	COUNTRY	CITY	NAME	GENDER
1	ITALY	ROME	Ken	MALE
2	FRANCE	LION	Jonathan	MALE
3	ITALY	FLORENCE	Paul	MALE
4	FRANCE	LION	Lucie	FEMALE
5	ITALY	ROME	Silvie	FEMALE
6	UK	LONDON	Jon	MALE
7	FRANCE	PARIS	Corinne	FEMALE
8	FRANCE	LION	Octavia	FEMALE

First queries counting occurence of duplicate row with same Country and City

(SELECT country,city, count(*) counting
  FROM Customers
  GROUP BY
    country,city
HAVING
    COUNT(*) > 1
    order by counting)Counter

This is result:

COUNTRY	CITY	COUNTER	COUNTER
ITALY	ROME	2	2
FRANCE	LION	2	3

Showing all duplicate records with relative ID:

select Customers.ID,Country, City,Name,	Gender,Counter.counting
from Customers,(SELECT country,city, count(*) counting
  FROM Customers
  GROUP BY
    country,city
HAVING
    COUNT(*) > 1
    order by counting)Counter
where Counter.Country=Customers.Country and
      Counter.city= Customers.city
    order by  counting desc,Customers.ID desc,Counter.country,Counter.city

and final table result:

ID	COUNTRY	CITY	NAME	GENDER	COUNTER
1	ITALY	ROME	Ken	MALE	2
5	ITALY	ROME	Silvie	FEMALE	2
2	FRANCE	LION	Jonathan	MALE	3
4	FRANCE	LION	Lucie	FEMALE	3
8	FRANCE	LION	Octavia	FEMALE	3

Table shows column ID rapresent position into table and counter with number of duplicate row.

Rows ID 1,5 are same records but difference for name and  gender, two occurrences.

Row ID 2,4,8 are same recordsfor Country and City but difference for name and  gender, tree occurrences.

Changing where condition, change result, if gender is female:

select Customers.ID,Country, City,Name,	Gender,Counter.counting
from Customers,(SELECT country,city, count(*) counting
  FROM Customers
  GROUP BY
    country,city
HAVING
    COUNT(*) > 1
    order by counting)Counter
where Counter.Country=Customers.Country and
      Counter.city= Customers.city and
      Customers.Gender='FEMALE'
    order by  counting desc,Customers.ID desc,Counter.country,Counter.city

Result Table:

ID	COUNTRY	CITY	NAME	GENDER	COUNTER
4	FRANCE	LION	Lucie	FEMALE	2
8	FRANCE	LION	Octavia	FEMALE	2

 

see also:

 Duplicate row