John Kopanas wrote:
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.
Really depends on how big the table is and whether it's properly
indexed. I believe group by sorts for you so you shouldn't have to put
that order by clause. I tested this on a table with over a million
records and over 2.8 gigs worth of data and it was fairly quick so maybe
you bad hardware or improper config settings. there's really a lot of
things it could be. What storage engine are you using? What is the
output of "show create table jobs;"? What settings do you have specified
in your my.cnf?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
