Retrieve duplicate rows

homiesite · #1 (**permalink**) 08-15-11, 11:44

Hi,

I am trying to retrieve just the rows that have a duplicate column value from a distinct result set.
For example.....

If I do SELECT DISTINCT col1, col2 FROM table I get....

col1 col2
1...... a
2.......b
2...... c
3...... d

From these results I am trying to retrieve just the rows that have duplicates in col1, in this case just the rows where col1 has a value of 2. After everything I have tried, I can't get just these 2 rows. Any help would be greatly appreciated. Thanks in advance.

Regards,
Jay

shammat · #2 (**permalink**) 08-15-11, 13:46

Something like this:

Code:

SELECT col1
FROM the_table
GROUP BY col1
HAVING count(*) > 1

Pat Phelan · #3 (**permalink**) 08-15-11, 14:12

It seems that r927 and shammat are solving a different problem than what I'm reading. My suggestion would be:

Code:

SELECT DISTINCT col1, col2
   FROM table AS a
   WHERE EXISTS (SELECT *
      FROM table AS b
      WHERE  b.col1 = a.col1
         AND  b.col2 <> a.col2)

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech