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 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.
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.
Okay, so here's what I tried. I created a view vLights from:
SELECT MAX(lights.date),machine.mach_name FROM lights JOIN machine USING
(mach_id) GROUP BY mach_name;
Now, as I use the query by itself I get this:
mysql> SELECT MAX(lights.date),machine.mach_name FROM lights JOIN 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)
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.
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.
--
Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
