You have your count in the wrong spot. I'm not even sure how that
query would work. The count(*) should be part of a select field. By
putting it in the HAVING clause it's calculating it after the query
runs.
SELECT jobs.*, count(*) AS Cnt FROM jobs GROUP BY customer_number,
job_number HAVING
Cnt > 1 ORDER BY customer_number;
You are doing a full table scan though, so it is going to be limited
by the speed of you disk. You should have an index on customer_number
+job_number if this is run frequently. If you don't need all the
fields, then specify just the ones you need instead on *. If you only
need the fields that are already in the index, then MySQL doesn't
need to access the table (although that may be only InnoDB tables).
On Jul 29, 2007, at 10:31 PM, 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.
--
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/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
