Select partitions of rows where columns have equal value

Question

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.

Why is group 4 in the expected result?

The Impaler
– The Impaler

2022-04-27 13:32:08 +00:00
Commented Apr 27, 2022 at 13:32 — The Impaler
– The Impaler, Commented Apr 27, 2022 at 13:32

The Impaler · Accepted Answer · 2022-04-27 13:30:33Z

1

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
)

answered Apr 27, 2022 at 13:30

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jimangel2001 Over a year ago

Thank you! I didn't know I could do count(distinct <column_name>)

Collectives™ on Stack Overflow

Select partitions of rows where columns have equal value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related