Select partitions of rows where columns have equal value-CodePudding

I have data grouped (column_2) and I want to select only the rows where for each group (same column_2 value) all the values at another column (column_3) is equal. There are more columns with no distinct values (such as column_1) but we don't care about their values.

For example, for this table:

| column_1   | column_2 | column_3 |
----------------------------------
|'irrelevant'|    1     |    5     |
|'irrelevant'|    1     |    5     |
|'irrelevant'|    1     |    5     |
|'irrelevant'|    2     |    3     |
|'irrelevant'|    2     |    5     |
|'irrelevant'|    2     |    5     |
|'irrelevant'|    3     |    7     |
|'irrelevant'|    3     |    7     |
|'irrelevant'|    4     |    8     |
|'irrelevant'|    4     |    9     |
|'irrelevant'|    4     |    2     |
|'irrelevant'|    5     |    6     |
|'irrelevant'|    6     |    2     |
|'irrelevant'|    7     |    1     |
|'irrelevant'|    7     |    1     |
|'irrelevant'|    7     |    1     |

I'd get:

| column_1   | column_2 | column_3 |
----------------------------------
|'irrelevant'|    1     |    5     |
|'irrelevant'|    1     |    5     |
|'irrelevant'|    1     |    5     |
|'irrelevant'|    3     |    7     |
|'irrelevant'|    3     |    7     |
|'irrelevant'|    5     |    6     |
|'irrelevant'|    6     |    2     |
|'irrelevant'|    7     |    1     |
|'irrelevant'|    7     |    1     |
|'irrelevant'|    7     |    1     |

The rows are skipped where, for the same column_2 value, the column_3 is not the same across them.

CodePudding user response：

You can use COUNT(DISTINCT <expr>) to find out how many different values are there per group.

For example:

select *
from t
where column_2 in (
  select column_2 
  from t 
  group by column_2 
  having count(distinct column_3) = 1
)