List:General Discussion« Previous MessageNext Message »
From:Mark Haney Date:September 7 2012 12:13pm
Subject:Re: Create a VIEW with nested SQL
View as plain text  
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:

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
markh@stripped
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
Thread
Create a VIEW with nested SQLMark Haney6 Sep
  • Re: Create a VIEW with nested SQLhsv6 Sep
    • Re: Create a VIEW with nested SQLMark Haney7 Sep
    • Re: Create a VIEW with nested SQLMark Haney7 Sep
      • Re: Create a VIEW with nested SQLhsv8 Sep
      • RE: Create a VIEW with nested SQLRick James10 Sep
        • RE: Create a VIEW with nested SQLhsv11 Sep
  • RE: Create a VIEW with nested SQLRick James7 Sep