Re: Returning All Rows That Repeat

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 <johnt@holmescorp.com> wrote:
> >> From: John Kopanas [kopanas@gmail.com]
> >> 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
john@kopanas.com
http://www.kopanas.comhttp://www.cusec.nethttp://www.soen.info

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com