Does it makes sense that on a table of 100,000 rows that my DB is crapping out with the following query?
SELECT * FROM jobs GROUP BY customer_number, job_number HAVING count(*) > 1 ORDER BY customer_number; :-) On 7/29/07, John Trammell <[EMAIL PROTECTED]> wrote: > >> From: John Kopanas [EMAIL PROTECTED] > >> Subject: Returning All Rows That Repeat > >> > >> I want to be able to return all rows that have one or more other rows > >> with the same customer_number and job_number. > >> > >> So for instance. If their are two jobs in my jobs table with a > >> customer_number = '0123' and job_number ='12' then I want both of > >> those jobs to return one right after another so I can compare their > >> other fields. And I want to run a query once a day over the whole > >> table to see if their are any repeats. > > One way would be a self-join, e.g.: > > SELECT * > FROM mytable t1, mytable t2 -- same table twice > WHERE t1.customer_number = t2.customer_number -- with same cust. no. > AND t1.job_number = t2.job_number -- and same job no. > AND t1.id <> t2.id; -- but the records are distinct > > INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE > PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If > you are not an intended recipient of this message, or an agent responsible > for delivering it to an intended recipient, you are hereby notified that you > have received this message in error, and that any review, dissemination, > distribution, or copying of this message is strictly prohibited. If you > received this message in error, please notify the sender immediately, delete > the message, and return any hard copy print-outs. > -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
