getting certain rows from a group by

2012-09-18 Thread Larry Martell
I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0

Php programmer

2012-09-18 Thread ratlhaganam
Hi, I need help, I'm working on a project that need time query in Mysql. I want to display a time in php page, that took mysql to execute 100 000 records Sent from my BlackBerry® wireless device

Re: getting certain rows from a group by

2012-09-18 Thread Peter Brawley
On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run,

Re: getting certain rows from a group by

2012-09-18 Thread Larry Martell
On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN

[ANN] ODB C++ ORM 2.1.0 released

2012-09-18 Thread Boris Kolpackov
Hi, I am pleased to announce the release of ODB 2.1.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code.

RE: [Possible Spam]Php programmer

2012-09-18 Thread Steven Staples
-Original Message- From: ratlhaga...@yahoo.com [mailto:ratlhaga...@yahoo.com] Sent: September 18, 2012 7:31 AM To: mysql@lists.mysql.com Subject: [Possible Spam]Php programmer Hi, I need help, I'm working on a project that need time query in Mysql. I want to display a time in

mysqldump warning

2012-09-18 Thread nixofortune
Hello everybody. I'm trying to create a backup of mysql database: mysqldump --all-databases --routines --master-data=2 all_databases_`date +'%y%m%d-%H%M'`.sql It looks like backup has been created but I've got this Warning: Warning: mysqldump: ignoring option '--databases' due to invalid value

RE: Are Single Column Indexes are sufficient

2012-09-18 Thread Rick James
WHERE (t0.job_id = '006-120613043532587-o-C') AND t0.bean_type = 'ActionItems'; Begs for INDEX(job_id, bean_type) -- in either order where job_id = '0043189-120805203721153-o-C' and nominal_time = '2012-09-07 07:16:00' and nominal_time '2012-09-07

RE: getting certain rows from a group by

2012-09-18 Thread Rick James
For single query... Plan A: Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in WHERE bottom = ( SELECT MIN(bottom) ... ) Plan B: FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON

Re: getting certain rows from a group by

2012-09-18 Thread Larry Martell
On Tue, Sep 18, 2012 at 7:56 AM, Larry Martell larry.mart...@gmail.com wrote: On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP,

Re: getting certain rows from a group by

2012-09-18 Thread Larry Martell
On Tue, Sep 18, 2012 at 2:05 PM, Rick James rja...@yahoo-inc.com wrote: For single query... Plan A: Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in WHERE bottom = ( SELECT MIN(bottom) ... ) Plan B: FROM ( SELECT @min = MIN(bottom), @max =

RE: getting certain rows from a group by

2012-09-18 Thread Rick James
SELECT mn.* mx.* FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max That is, the FROM finds the value for the desired row. The JOIN then gets to the rest of the fields. Caveat:

Re: getting certain rows from a group by

2012-09-18 Thread Larry Martell
On Tue, Sep 18, 2012 at 4:01 PM, Rick James rja...@yahoo-inc.com wrote: SELECT mn.* mx.* FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max That is, the FROM finds the value

RE: getting certain rows from a group by

2012-09-18 Thread Rick James
SELECT ((the appropriate id)) -- -- FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id =

Re: function INTERVAL in view

2012-09-18 Thread hsv
2012/09/17 13:11 -0500, Peter Brawley Looks like a bug. Report it? It was reported: Bug #45346 VIEW containing INTERVAL(...) can be created but does not work Submitted: 5 Jun 2009 10:00 Modified: 5 Jun 2009 10:16 Severity:S3 (Non-critical) Version:6.0, 5.4, 5.1 OS:Any Seems it arouses

Re: Making myself crazy...

2012-09-18 Thread Jan Steinman
Thanks for your help, Rick. The solution that seems to be working was to have both the FROM and LEFT JOIN be grouped subqueries. I realize this is probably not the best way of doing things, but it seems fast enough at present. Here is the query, followed by the table definitions, resulting in