HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
distinctsamethecolumnwhicheveryothervalueforfind

Problem

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  |
    +---+----+

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   4

Code 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   4

Context

StackExchange Database Administrators Q#163635, answer score: 2

Revisions (0)

No revisions yet.