List:General Discussion« Previous MessageNext Message »
From:hsv Date:September 6 2012 2:23pm
Subject:Re: Create a VIEW with nested SQL
View as plain text  
>>>> 2012/09/06 08:50 -0400, Mark Haney >>>>
>SELECT vLight.* FROM
>(SELECT lights.*, machine.mach_name from lights
>JOIN machine ON lights.mach_id = machine.mach_id
>ORDER BY date DESC) as vLight
>GROUP BY mach_id
<<<<<<<<
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. 

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