Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: Retrieve duplicate rows

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Something like this:
    Code:
    SELECT col1
    FROM the_table
    GROUP BY col1
    HAVING count(*) > 1

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •