<p>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 <code>COL1</code> and <code>COL2</code> only. </p>

<p>So let's say I have something like this:</p>

<pre><code> 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
</code></pre>

<p>I need a SQL query that returns this:</p>

<pre><code> COL1   COL2   COL3
 ---------------------
 aa     111    blah_x
 aa     111    blah_j
 bb     112    blah_d
 bb     112    blah_d
</code></pre>

Join on yourself like this:
```
```
SELECT a.col3, b.col3, a.col1, a.col2 
FROM tablename a, tablename b
WHERE a.col1 = b.col1 AND a.col2 = b.col2 AND a.col3 != b.col3
```
```
If you're using postgresql, you can use the oid to make it return less duplicated results, like this:
```
```
SELECT a.col3, b.col3, a.col1, a.col2 
FROM tablename a, tablename b
WHERE a.col1 = b.col1 AND a.col2 = b.col2 AND a.col3 != b.col3
  AND a.oid < b.oid
```
```