CODEWITHSUNDEEP
sqlsql-servert-sql
HelloWorld1
HelloWorld1

Reputation: 14100

Display Duplicated Rows with different City

Goal: Display row that has similar 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.

Upvotes: 2

Views: 69

Answers (3)

Siamak Ferdos
Siamak Ferdos

Reputation: 3299

Try this code :

DECLARE @TempTable TABLE
    (
      [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' );

SELECT DISTINCT
        A.Address ,
        A.Zipcode ,
        A.City
FROM    @TempTable A
        INNER JOIN #TempTable B ON A.Address = B.Address
                                   AND A.Zipcode = B.Zipcode
                                   AND A.City <> B.City;

The Result is:

Biskop Svanes Vej 5 3460    Birkerød Ruteplan
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

Upvotes: 1

dnoeth
dnoeth

Reputation: 60472

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

Upvotes: 1

Hadi
Hadi

Reputation: 37313

You can use Subqueries to achieve this

select Distinct Address, Zipcode, City
from #Temptable
Where exists (select  Address, Zipcode 
                         from #Temptable T1 
                         where T1.address = address and T1.zipcode = zipcode 
                        group by Address, Zipcode 
                        having count(*) > 1)
order by Address, Zipcode, City

Upvotes: 0

Related Questions