Re: getting certain rows from a group by

2012-09-18 Thread hsv
2012/09/18 06:53 -0400, Larry Martell This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row w

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 t

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

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 = data_cst.target_na

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 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 for t

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. C

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 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 = MAX(bottom), ... )

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 wrote: > On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley > 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, >>>

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 bottom

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 08:06

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: [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

[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. Maj

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 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 reruns > 0 T

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",

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

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