2012/09/10 15:49 -0700, Rick James
SELECT ... ORDER BY .. GROUP BY..
is syntactically incorrect.
Yeap, my mistake.
( SELECT ... ORDER BY .. ) GROUP BY ..
Is what I call the "group by trick". It is an optimal way to SELECT all the
fields corresponding to the MAX (or M
wing it up. MariaDB decides that this construct can
be optimized, and messes up the 'trick'.
> -Original Message-
> From: Mark Haney [mailto:ma...@abemblem.com]
> Sent: Friday, September 07, 2012 6:12 AM
> To: mysql@lists.mysql.com
> Subject: Re: Create a VIEW wi
2012/09/07 09:11 -0400, Mark Haney
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.
Somehow I'm missing something incredibly obvious here.
That is not certain.
There is a fairly standard, fairly ug
VIEWs have never been optimized well. Avoid them.
Please provide SHOW CREATE TABLE for each table.
Is `machine` used for anything other than the machine_name?
Are you expecting one row?
Here's another way:
SELECT lights.*,
( SELECT mach_name
FROM machine
WHE
On 09/06/2012 10:23 AM, h...@tbbs.net wrote:
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
On 09/06/2012 10:23 AM, h...@tbbs.net wrote:
How about
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" rea
2012/09/06 08:50 -0400, Mark Haney
>SELECT vLight.* FROM
>(SELECT lights.*, machine.mach_name from lights
>JOIN machine ON lights.mach_id = machine.mach_id
>ORDER BY date DESC) as vLight
>GROUP BY mach_id
How about
SELECT lights.*, machine.mach_name
FROM lights JOIN machine USIN
