Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: counting duplicate values

    I know this question has been asked before but this is a little different and I can't seem to get my head around it right now.
    What i have is a table like so:

    ID1 ID2 DESC
    100 24 something1
    100 24 this is a test
    100 24
    100 25 somethingelse
    101 36 something
    101 37 something else altogether

    What i need is to determine which ID1 value has the same ID2 value listed more than once WITH the description filled in. If there is no description filled in even though the ID2 may be listed twice on one ID1, then it's ok and don't want it displayed in the query.

    So, in this case, ID1 of 100 has ID2 24 listed twice with a description on both ID2's (of 24). this is what I need to show up in the query like so:

    ID1 ID2 DESC
    100 24 something1
    100 24 this is a test

    Could someone give me a hand with this? I've found similiar problems...but haven't been able to apply those answers to this.

    Thx

  2. #2
    Join Date
    Aug 2004
    Posts
    54
    Use something like r937's select statement in this post.

    Have some fun.

  3. #3
    Join Date
    May 2004
    Posts
    125
    Try this:

    select id1, id2 into #tmpGroup from table1
    group by id1, id2
    having count(1)>1


    select id1, id2, desc from table1 t
    inner join @tmpGroup tmp on t.id1=tmp.id1 and t.id2=tmp.id2

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I usually do this with a subquery rather than a temp table:

    select YourTable.*
    from YourTable
    inner join
    (select ID1, ID2
    from YourTable
    where DESC is not null
    group by ID1, ID2
    having count(*) > 1) Dups
    on YourTable.ID1 = Dups.ID1 and YourTable.ID2 = Dups.ID2

    Same method, though....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2003
    Posts
    73

    Talking

    thx a ton yall! Once again, dBforums and Co has come to my rescue. I used blindman's example and tweaked it just a little for what I'm working on. I had to add on a "WHERE desc NOT LIKE '' " instead of the "desc is not null" in my situation. All i have to do now is only display exact duplicates in the instructions.

    thx again.

  6. #6
    Join Date
    Oct 2003
    Posts
    73
    btw, are they ever gonna change this site so you can do more searches instead of having to wait 45 seconds all the time?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have zero length strings in DESC.

    where len(DESC) > 0
    ...may be a little more efficient than NOT LIKE.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2003
    Posts
    73

    Thumbs up

    thx, I'll give that a try. I got the query running exactly the way I want it to. Thx again yall.

Posting Permissions

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