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!

ElizabethI'd suggest: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|||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. :mad:|||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. :mad: 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|||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. :shocked:|||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|||Maybe we could point them to this link (http://www.dbforums.com/showthread.php?t=1031644) (or similar). :beer: