What's the SQL query to list all rows that have 2 column sub-rows as duplicates?

Question

I have a table that has redundant data and I'm trying to identify all rows that have duplicate sub-rows (for lack of a better word). By sub-rows I mean considering COL1 and COL2 only.

So let's say I have something like this:

 COL1   COL2   COL3
 ---------------------
 aa     111    blah_x
 aa     111    blah_j
 aa     112    blah_m
 ab     111    blah_s
 bb     112    blah_d
 bb     112    blah_d
 cc     112    blah_w
 cc     113    blah_p

I need a SQL query that returns this:

 COL1   COL2   COL3
 ---------------------
 aa     111    blah_x
 aa     111    blah_j
 bb     112    blah_d
 bb     112    blah_d

Answer 1

Does this work for you?

select t.* from table t
left join ( select col1, col2, count(*) as count from table group by col1, col2 ) c on t.col1=c.col1 and t.col2=c.col2
where c.count > 1