4

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

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 – RayofCommand Feb 8 '17 at 22:04
  • I'm glad to be able to help. – McNets Feb 8 '17 at 22:05
1

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)
1

This is a good question with probably multiple answers!

Heres my quick answer:

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. – RayofCommand Feb 8 '17 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. – blobbles Feb 8 '17 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.