On 09/06/2012 10:23 AM, hsv@stripped 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:
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.
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux