Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    28

    Unanswered: displaying all records of duplicates

    HI all I have this table below and i need to get records from col1 >1 and col2 >1

    col1 col2 language
    1 a s
    1 b p
    3 c e
    3 c f

    SELECT col1, col2, language COUNT(*) AS Expr1
    FROM table1
    GROUP BY col1, col2
    HAVING (COUNT(*) > 1)

    with the above query it gives me this

    col1 col2 expr1
    3 c 2

    the language column is not displayed what i want is not the exact count but all the records which is duplicate and must resemble the result below

    col1 col2 expr1 language
    3 c 2 e
    3 c 2 f

    I need the result above inorder to normalize the table and bring out the language part into another table

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.col1, t.col2, d.daCount, t.language 
      FROM table1 AS t
    INNER
      JOIN (
           SELECT col1, col2, COUNT(*) AS daCount
             FROM table1                                  
           GROUP BY col1, col2                          
           HAVING COUNT(*) > 1
           ) as d     
        ON c.col1 = t.col1
       AND d.col2 = t.col2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    28
    thanks Rudy

    Got it...

Posting Permissions

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