CODEWITHSUNDEEP
sqlsql-serverselect
Kiril
Kiril

Reputation: 40395

Select all records where column B only has distinct instances of column A

I don't know how to explain this situation, it's a bit tricky so I'll try to give you an example of the data I have and the output I need:

CREATE TABLE #TempTable( A int, B int)
INSERT INTO #TempTable (A,B)
VALUES 
(1,1),
(1,2),
(1,2),
(1,4),
(3,2),
(3,2),
(3,2),
(3,3),
(3,3);

I can't figure out how to select all records such that the final output is:

1,1
1,4
3,3
3,3

I need the records where column B only has distinct instances of column A. All other instances should be ignored... I feel like this should be pretty simple, but I'm blanking out and I can't figure it out.

Update:

I've been having a hard time explaining this, but I think this is the correct logic: you can have duplicate instances of B for a given A, but you can't have duplicate instances of B across different A's.

Upvotes: 0

Views: 310

Answers (4)

user359040
user359040

Reputation:

Try:

with cte as
(select t.*, min(A) over (partition by B) minA, max(A) over (partition by B) maxA
 from #TempTable t)
select A, B
from cte
where minA=maxA

Upvotes: 2

Jakub Kania
Jakub Kania

Reputation: 16487

SELECT t1.A, t1.B
  FROM TempTable t1
  LEFT JOIN TempTable t2
    ON t1.B=t2.B AND t1.A<>t2.A
WHERE t2.A IS NULL;

http://sqlfiddle.com/#!6/d235a/5

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125660

SELECT A, B FROM TempTable
WHERE B IN (
  SELECT B
  FROM TempTable
  GROUP BY B
  HAVING COUNT(DISTINCT A) = 1
)

Working example on MS SQL 2012: http://sqlfiddle.com/#!6/d235a/4

Upvotes: 2

Dan Metheus
Dan Metheus

Reputation: 1438

SELECT A, B, COUNT(A) as NumPairs 
FROM TempTable 
GROUP BY A, B 
HAVING COUNT(A)=1

SQLFiddle - http://sqlfiddle.com/#!6/ee6c5/9

Upvotes: 1

Related Questions