Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    26

    Arrow Unanswered: looking for dups SQL statement

    Hi -

    I am looking for help to write a SQL statement that looks for duplicates.
    I used to know how to do this, so I know it can be done.
    It's a SQL statement that uses an embedded SELECT with a GROUP BY and COUNT, then a HAVING clause where the count > 1

    In my case, I am starting with this statement

    SELECT hhld_id, mail_dt, quote_num, count(*) as count_dups from response
    group by hhld_id, mail_dt, quote_num

    I am only interested in results having count_dups > 1

    How can I achieve these results? It is a very large table (almost 100 million, so it times out when I try and return all results)

    Thanks for your help!

    Elizabeth

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest:
    Code:
    SELECT hhld_id, mail_dt, quote_num
    ,  Count(*) as count_dups
       FROM response
       GROUP BY hhld_id, mail_dt, quote_num
       HAVING 1 < Count(*)
       ORDER BY hhld_id, mail_dt, quote_num
    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down



    This question that has been asked and answered hundreds if not thousands of times in this and other forums. You should have done some research and "searched" for the answer you needed.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by LKBrwn_DBA
    This question that has been asked and answered hundreds if not thousands of times in this and other forums. You should have done some research and "searched" for the answer you needed.
    You are certainly correct, this question is often asked and usually answered. The problem seems to be that users don't know exactly what to ask until after they know the answer.

    It was a civil question, and showed that the user had done a reasonable job of thinking out the problem and expressing their question. I understand your frustration at seeing the same question repeatedly (I'm not overly enthused about it myself), but until we can find a way that users can relatively reliably find the answer for themselves, I will probably just go on answering it!

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    I didn't intend to be so harsh, but I beleive there also may be a way to instruct people to do some research before posting questions that have been answered many times in the past.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh I'm with you on this one, I just can't find the "magic bullet" to get the job done.

    I'm not at all sure that I've even given the user anything that they didn't have before. They had all of the pieces, and by offering my first guess at a solution all I really did was start a conversation rolling... If they respond and say "thanks" I'll be surprised. If they point out what's bothering them about my idea, at least I'll know a bit more toward helping them find a solution.

    My gut feel is that this poster is pretty savvy. I don't think we've even scratched the surface of the real problem underlying this post, but at least now we've got the potential for a conversation, which is more than we had!

    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs up



    Maybe we could point them to this link (or similar).

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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