patternsqlMinor
Find values which occur in every row for every distinct value in other column of the same table
Viewed 0 times
distinctsamethecolumnwhicheveryothervalueforfind
Problem
I need a select statement which finds values from Column
I don't know which values are in 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 |
+---+----+Solution
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 4Code Snippets
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 4Context
StackExchange Database Administrators Q#163635, answer score: 2
Revisions (0)
No revisions yet.