What'sUP What'sUP - 1 year ago 51
SQL Question

Display Duplicated Rows with different City

Goal:
Display row that has similiar address and zipcode but not City

Requested result below:

Address Zipcode City
Biskop Svanes Vej 5 3460 Birkerød Ruteplan
Biskop Svanes Vej 5 3460 København S
Biskop Svanes Vej 5 3460 Holte
Peter Bangs Vej 30 2000 Frederiksberg
Peter Bangs Vej 30 2000 Holte
Kigkurren 8 R 2300 København
Kigkurren 8 R 2300 Køge


All data:

Lyneborggade 9 2300 København S
Biskop Svanes Vej 5 3460 Birkerød Ruteplan
Brogade 2 4300 Holbæk
Grønnevej 7 4600 København S
Kalkbrænderiløbskaj 4 2100 København Ø
Lyngbakkevej 14 2840 Holte
Peter Bangs Vej 30 2000 Frederiksberg
Biskop Svanes Vej 5 3460 Holte
Biskop Svanes Vej 5 3460 København S
Kigkurren 8 R 2300 København
Kigkurren 8 R 2300 Køge


Sourcecode:

create table #TempTable
(
Address nvarchar(80),
Zipcode nvarchar(50),
City nvarchar(50),
)

INSERT INTO #TempTable
(Address, Zipcode, City)
VALUES
('Lyneborggade 9','2300','København S'),
('Biskop Svanes Vej 5', '3460', 'Birkerød Ruteplan'),
('Brogade 2', '4300', 'Holbæk'),
('Grønnevej 7', '4600', 'København S'),
('Kalkbrænderiløbskaj 4', '2100', 'København Ø'),
('Lyngbakkevej 14', '2840', 'Holte'),
('Peter Bangs Vej 30', '2000', 'Frederiksberg')
('Biskop Svanes Vej 5','3460','Holte')
('Biskop Svanes Vej 5', '3460', 'København S',)
('Kigkurren 8 R', '2300', 'København',)
('Kigkurren 8 R', '2300', 'Køge',)


Problem:
I don't know how to retrieve it.

Answer Source

You can use an EXISTS-subquery:

select Address, Zipcode, City
from #Temptable as T
Where exists
 ( select *
   from #Temptable as T1
   where T1.address = T.address  -- same address & zip
     and T1.zipcode = T.zipcode 
     and T1.city <> T.city       -- but different city
 )
order by Address, Zipcode, City

Or a Group Count:

WITH cte AS
 ( select t.*,  
      COUNT(*)
      OVER (PARTITION BY Address, Zipcode) AS cnt 
   from #Temptable T1 
 )
select Address, Zipcode, City
from cte
where cnt > 1 
order by Address, Zipcode, City
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download