CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Flip Booth
Flip Booth

Reputation: 271

Select rows with same column A but different column B

ID          Zip        Room
----------- ---------- ------
317         94087      S105
318         94087      L603
1739        94404-1801 L603
1823        94401-2129 L603
1824        94401-2129 L603
2135        94404-1801 L603
2268        95136-1459 S604
2269        95136-1459 S604
3704        92673-6417 L402
4479        93454-9670 L402
4480        93454-9670 L402
4782        92395-4681 L402
4783        92395-4681 L402
4852        92886-4411 L402
4853        92886-4411 L402
4959        92673-6417 L402
5153        91773-4028 L402
5202        91773-4028 L402
5211        91765-2959 L402
5212        91765-2959 L402
5388        92336-0605 L402
5392        92336-0605 L402
5727        92870      L402
5728        92870      L402
5831        92557      L402
5916        92557      L402

How do I select ID's that has THE SAME zip but different Room ?

In the table above, I want the result to be:

ID          Zip        Room
----------- ---------- ------
317         94087      S105
318         94087      L603

Using SQL Server 2008

Upvotes: 7

Views: 6067

Answers (3)

Andriy M
Andriy M

Reputation: 77657

You could use the window aggregate functions MIN() and MAX() to get, correspondingly, the minimum and the maximum Room per Zip, then select only the rows where those values differ. Here:

WITH MinMax AS (
  SELECT
    *,
    MinRoom = MIN(Room) OVER (PARTITION BY Zip),
    MaxRoom = MAX(Room) OVER (PARTITION BY Zip)
  FROM atable
)
SELECT
  ID,
  Zip,
  Room
FROM MinMax
WHERE MinRoom <> MaxRoom
;

Upvotes: 0

Cris
Cris

Reputation: 5007

select * 
from table t1 
join table t2 
  on t1.Zip = t2.Zip and t1.Room <> t2.Room

Upvotes: 5

Tim Schmelter
Tim Schmelter

Reputation: 460048

You can use EXISTS:

SELECT ID, Zip, Room 
FROM dbo.Table t1
WHERE EXISTS
(
   SELECT 1 FROM dbo.Table t2
   WHERE t2.Zip = t1.Zip
   AND t2.Room <> t1.Room
)

Upvotes: 10

Related Questions