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 MIN) of one of the fields.
. 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 with nested SQL
On 09/06/2012 10:23 AM, h
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 really occur
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 more
...@abemblem.com]
Sent: Thursday, September 06, 2012 5:51 AM
To: mysql@lists.mysql.com
Subject: Create a VIEW with nested SQL
I have a bit of a performance/best practice question for those in the
know. I have a nested SQL statement that selects fields from a SELECT
that has a JOIN in it. Here's the SQL
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 ugly means of
I have a bit of a performance/best practice question for those in the
know. I have a nested SQL statement that selects fields from a SELECT
that has a JOIN in it. Here's the SQL:
SELECT vLight.* FROM
(SELECT lights.*, machine.mach_name from lights
JOIN machine ON lights.mach_id =
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 USING (mach_id)
/* ORDER
