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 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.
Now that's a syntax I've never seen before. Then again, I haven't done
any serious SQL in about 5 years, so I shouldn't be surprised. If I
read your question correctly about mach_id, the mach_id is precisely
what it's name implies, the identifier of a particular machine. It's
unique to that machine, but it occurs many times in the lights table, as
it is the table my monitoring system inserts data. I have them ORDERed
BY date DESC to make sure I get the most recent status of EACH machine
at the top of the list like this:
mach1,2010-09-01 10:00:00,running
mach2,2010-09-01 09:59:51,running
mach3....
etc.
That's also what the GROUP BY is for, to group all the entries by
machine ID, order them first, then group them. Honestly, the ORDER BY
may not be needed other than for getting them listed in 'numerical'
order. That's certainly not necessarily a requirement at the moment.
(And getting me to think about the grouping and ordering part of the
query makes me stop and rethink the logic behind the query. So thanks
for that. My SQL brain is still fuzzy, and combined with Vicodin, I'm
no House. I can't function 100% on pain meds.
I'll take a look at this, and if I need any help, I'll holler, but this
looks really good at the moment.
Thanks.
--
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
