From: Rick James
Date: September 10 2012 10:49pm
Subject: RE: Create a VIEW with nested SQL
List-Archive: http://lists.mysql.com/mysql/228161
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148B8897CE@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

SELECT ... ORDER BY .. GROUP BY..=20
is syntactically incorrect.

( SELECT ... ORDER BY .. ) GROUP BY ..
Is what I call the "group by trick".  It is an optimal way to SELECT all th=
e fields corresponding to the MAX (or MIN) of one of the fields.  But it de=
pends on the optimizer not screwing it up.  MariaDB decides that this const=
ruct can be optimized, and messes up the 'trick'.



> -----Original Message-----
> From: Mark Haney [mailto:markh@stripped]
> Sent: Friday, September 07, 2012 6:12 AM
> To: mysql@stripped
> Subject: Re: Create a VIEW with nested SQL
>=20
> On 09/06/2012 10:23 AM, hsv@stripped wrote:
>=20
> > SELECT lights.*, machine.mach_name
> > FROM lights JOIN machine USING (mach_id)
> > /* ORDER BY date DESC */
> > GROUP BY mach_id
> >
> > ? With USING the fields "mach_id" from "lights" and "machine" become
> one unambiguous field "mach_id".
> >
> > Does "mach_id" really occur more times in "lights" or "machine"? If
> only once in both tables, GROUP BY at most orders it. It is not clear
> that ORDER BY does anything with GROUP BY outside it, unless "mach_id"
> is unique in both tables--I have found that GROUP BY not always orders
> the output, when everything is unique.
> >
> > And yes, MySQL balks at saving a view with a query for a table. One
> has to make them separate views.
> >
> >
>=20
> The problem I encountered is that I can't find a way to just pull the
> most recent records for each machine without the GROUP BY statement.
> That's all I need.
>=20
> Okay, so here's what I tried.  I created a view vLights from:
>=20
>=20
> > SELECT MAX(lights.date),machine.mach_name FROM lights JOIN machine
> > USING (mach_id) GROUP BY mach_name;
>=20
> Now, as I use the query by itself I get this:
>=20
> > mysql> SELECT MAX(lights.date),machine.mach_name FROM lights JOIN
> > mysql> machine USING (mach_id) GROUP BY mach_name;
> > +---------------------+-----------+
> > | MAX(lights.date)    | mach_name |
> > +---------------------+-----------+
> > | 2012-09-07 09:03:10 | #10       |
> > | 2012-09-07 09:03:07 | #12       |
> > +---------------------+-----------+
> > 2 rows in set (3.62 sec)
>=20
> This is better than the 20s+ I was getting before, but still not
> acceptable for only 2 machines when I'll have 40+ at production time.
>=20
> All I need is the most recent record for EACH machine ID, THEN to pull
> the machine name from the table that has the name in it.
>=20
> Somehow I'm missing something incredibly obvious here.
>=20
> --
>=20
> Mark Haney
> Software Developer/Consultant
> AB Emblem
> markh@stripped
> Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql