CODEWITHSUNDEEP
sqlsql-server
Anna
Anna

Reputation: 96

How to select duplicates from a table

Considering this example of a very large table named 'Customer':

CustomerID   | LastName | FirstName | Address | City |
-------------|----------|-----------|---------|------|
1520         | Guere    |Francisco  |XPTO     |London|**
2214         | Santos   |António    |TUDO     |Porto |
3998         | Freixe   |Luís       |IUPO     |Quebec|
1520         | Guere    |Francisco  |XPTO     |Rome  |**
5691         | Anton    |Ana        |TIDI     |Lisbon|
1520         | Guere    |Francisco  |XPTO     |Rome  |**

As you can see from this small example, for the same customer (CustomerID = 1520) there is different values in the City column.

For example, the desired output in this case would be:

 CustomerID | City |
 -----------|------|
 1520       |London|
 1520       |Rome  |

I tried to develop a query that return the CustomerID that have different values in the City column. 

SELECT CustomerID, City 
FROM (SELECT CustomerID, COUNT(DISTINCT City) FROM Customer GROUP BY CustomerID) 
WHERE City > 1

Can someone help me on what's wrong with the query i developed?

Upvotes: 0

Views: 52

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272376

You can use the following:

SELECT CustomerID, City
FROM Customer
WHERE CustomerID IN (
  SELECT CustomerID
  FROM Customer
  GROUP BY CustomerID
  HAVING COUNT(DISTINCT City) > 1
)
GROUP BY CustomerID, City;

Result:

| CustomerID | City   |
|------------|--------|
| 1520       | London |
| 1520       | Rome   |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can use exists if you want the cities:

select c.*
from customers c
where exists (select 1
              from customers c2
              where c2.customerid = c.customerid and c2.city <> c.city
             );

Or you can use group by, if you just want the customers:

select customerid
from customers
group by customerid
having min(city) <> max(city);

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

You could use HAVING to filter:

SELECT *
FROM Customer
WHERE CustomerID IN(SELECT CustomerID
                    FROM Customer 
                    GROUP BY CustomerID
                    HAVING COUNT(DISTINCT City) > 1);

Upvotes: 1

Related Questions