mssql - retrieve unique values of a column based on another column

Question

I have a table with two columns: ColumnA, ColumnB, with rows:

| A | 1 |
| B | 1 |
| B | 2 |
| C | 1 |
| C | 1 |
| C | 1 |
| A | 2 |
| B | 1 |
| A | 2 |
| A | 1 |

I would like to write a query that would return all unique values for ColumnB, for each unique value of ColumnA, where ColumnA has more than 1 value in ColumnB i.e.

| A | 1 |
| A | 2 |
| B | 1 |
| B | 2 |

C 1 should be omitted because there is only one distinct value for ColumnA = 'C'

There doesn't appear to be anything special about column A versus column B - it would appear you could just do `distinct columna,columnb` here. — Damien_The_Unbeliever, Sep 12 '17 at 07:51
sorry, i forgot to add the second part of my requirement, i.e. where ColumnA has more than 1 values in ColumnB — Panikos Stavrou, Sep 12 '17 at 07:54
@PanikosStavrou, You may have to re-write you question. it is a bit vague. for example, if you want to join tableA to TableB what column are you going to use? — JonWay, Sep 12 '17 at 08:03
@IvanP I don't want where, for example, A - 1 repeats more than one time. I want where for A I have more than one values (1, 2) — Panikos Stavrou, Sep 12 '17 at 08:08

Tim Schmelter · Accepted Answer · 2017-09-12T08:26:03.730

There might be a simpler approach but this works:

SELECT t.ColumnA, t2.ColumnB
FROM ( select ColumnA 
       from dbo.TableName t 
       group by t.ColumnA
       having count(distinct t.ColumnB) > 1) t
CROSS APPLY ( select distinct t2.ColumnB 
              from dbo.TableName t2 
              where t.ColumnA=t2.ColumnA ) t2

The first subquery returns all unique ColumnA values that have multiple (different) ColumnB values. The 2nd subquery returns all distinct ColumnB values of those ColumnA-values with CROSS APPLY.

IvanP · Answer 2 · 2017-09-12T08:42:36.413

2

SELECT DISTINCT * FROM x WHERE ColumnA IN(
    SELECT xd.ColumnA
    FROM (
        SELECT DISTINCT ColumnA, ColumnB FROM x
    ) xd
    GROUP BY xd.ColumnA HAVING COUNT(*) > 1 
)

SELECT y.ColumnA, y.ColumnB
FROM (
    SELECT ColumnA, ColumnB, COUNT(*) OVER (PARTITION BY  ColumnA) m
    FROM x 
    GROUP BY ColumnA, ColumnB
) y
WHERE m > 1

edited Sep 12 '17 at 08:42

answered Sep 12 '17 at 08:28

IvanP

263
7
21

mssql - retrieve unique values of a column based on another column

2 Answers2