# Find values which occur in every row for every distinct value in other column of the same table

I need a select statement which finds values from Column `B` which occurs in all distinct values for `A`. Meaning in my example it would return: `x` since `x` occurs in every value of `A`. (1,2,3 and 4)

I don't know which values are in Column `B` so I can't specify this in my query. How can I achieve this in SQL Server 2014?

``````    +---+----+
| A | B  |
+---+----+
| 1 | x  |
| 2 | x  |
| 3 | y  |
| 4 | y  |
| 3 | x  |
| 4 | x  |
+---+----+
``````

Count distinct A grouping by B, and return those where count(distinct A) match.

``````select B, count(distinct A)
from table_AB
group by B
having count(distinct A) = (select count(distinct A) from table_AB);

B   A
=== ===
x   4
``````
• this worked out of the box, thank you for the great answer sir Feb 8, 2017 at 22:04
• I'm glad to be able to help. Feb 8, 2017 at 22:05

You can do it using `row_number()` as well

``````drop table #Table1
CREATE TABLE #Table1
([A] int, [B] varchar(1))
;

INSERT INTO #Table1
([A], [B])
VALUES
(1, 'x'),
(2, 'x'),
(3, 'y'),
(4, 'y'),
(3, 'x'),
(4, 'x'),
(4, 'y'),
(5, 'y'),
(5, 'x')
;

;with cte as (
select *, row_number() over (partition by B order by (select null)) as rn
from #Table1
)
select B from cte
where rn = (select max(rn) from cte)
``````

This is a good question with probably multiple answers!

``````CREATE TABLE #Temp (
A int,
B char(1)
)
GO
INSERT INTO #Temp (A,B)
VALUES (1,'x'),(2,'x'),(3,'y'),(4,'y'),(3,'x'),(4,'x')
GO

WITH ATot AS (
SELECT SUM(DISTINCT A) as 'TotalA'
FROM #Temp
)
,
BTot AS (
SELECT B, SUM(A) as 'ValA'
FROM #Temp
GROUP BY B
)
SELECT b.B
FROM ATot a
INNER JOIN BTot b ON a.TotalA = b.ValA
``````
• thank you sir, unfortunately it didnt work out as expected in sql server. it returned only my column A empty. Feb 8, 2017 at 22:16
• Hmmm, that's odd, I built and ran it in SQL Server 2014 and it works just fine. Maybe you made a translation mistake? I will put them into "A" and "B" column names for clarity. Feb 8, 2017 at 23:03