Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: help on filtering double id

    Hi everybody..

    have this table and I want to filter only those records that has it's id's appearing more than one.


    table

    id field1

    1 ! first
    1 ! second
    2 ! first
    3 ! first
    3 ! second
    4 ! first

    the result should be

    id field1

    1 ! first
    1 ! second
    3 ! first
    3 ! second

    am using this query

    select field1, id, count(id) as countid
    FROM table1
    GROUP BY field1
    HAVING count(id) >1

    the countid column gives me always the value of one (don't know the reason) so I couldn't get the results I want

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The SELECT query that you posted won't work. Please post the query that you actually used.

    The problem that I think you are having is that you've specified both columns in your GROUP BY clause. This implies that each group will have only one member row unless there are two rows that have exactly the same ID and field1 values (both rows have identical values in all columns).

    If you can post the query that you actually used, I'm sure that someone can help you with it.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT table1.field1
         , table1.id
      FROM table1
    INNER
      JOIN ( SELECT id
               FROM table1
             GROUP 
                 BY id
             HAVING count(*) > 1 ) AS d
        ON d.id = table1.id
    Last edited by r937; 05-26-08 at 17:22.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MSSSMS
    Ambiguous column name 'id'.


    Code:
    SELECT    field1
            , id
    FROM    
            (
                SELECT      field1
                        , id
                        , countenstein    = COUNT(*)    OVER    (PARTITION BY    id)
                FROM    @table AS t
            ) AS countyvon
    WHERE    NOT    (countyvon.countenstein < 2)

  5. #5
    Join Date
    Aug 2006
    Posts
    87
    Quote Originally Posted by r937
    Code:
    SELECT table1.field1
         , table1.id
      FROM table1
    INNER
      JOIN ( SELECT id
               FROM table1
             GROUP 
                 BY id
             HAVING count(*) > 1 ) AS d
        ON d.id = table1.id

    thanks rudy got it with this the next solution suggested by pootle flump which I also tried is working on sql 2005 but not on sql 2000 which unfortunately we're still using .. thanks again guys

Posting Permissions

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