From: Brent Baisley (brenttechgmail.com)
Date: Wed Aug 01 2007 - 19:58:19 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <johntholmescorp.com> wrote:
>>>> From: John Kopanas [kopanasgmail.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
> johnkopanas.com
>
> 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?
> unsub=brenttechgmail.com
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]