List:General Discussion« Previous MessageNext Message »
From:Rick James Date:September 10 2012 10:49pm
Subject:RE: Create a VIEW with nested SQL
View as plain text  
SELECT ... ORDER BY .. GROUP BY.. 
is syntactically incorrect.

( 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.  But it depends on the optimizer
not screwing it up.  MariaDB decides that this construct can be optimized, and messes up
the 'trick'.



> -----Original Message-----
> From: Mark Haney [mailto:markh@stripped]
> Sent: Friday, September 07, 2012 6:12 AM
> To: mysql@stripped
> Subject: Re: Create a VIEW with nested SQL
> 
> On 09/06/2012 10:23 AM, hsv@stripped 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
> > mysql> 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
> markh@stripped
> 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

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