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

    Unanswered: Trying to retrieve rows with a duplicate column

    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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.col1
         , t.col2
      FROM ( SELECT col1
               FROM daTable
             GROUP
                 BY col1
             HAVING COUNT(*) > 1 ) AS d
    INNER
      JOIN daTable AS t
        ON t.col1 = d.col1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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