sql - Remove rows with distinct value in column 2 when grouped with column 1

Question

I have a table that looks like this

col 1	col 2
s	x
s	c
a	b
a	b
y	z
y	h
y	h
j	k

question from:https://stackoverflow.com/questions/65599222/remove-rows-with-distinct-value-in-column-2-when-grouped-with-column-1

Answer 1

You can use the analytical function count as follows

Select * from
(Select t.*,
       Count(distinct col2) over (partition by col1) as c
  From your_table t) t
 Where c > 1