List:General Discussion« Previous MessageNext Message »
From:hsv Date:September 11 2012 2:54am
Subject:RE: Create a VIEW with nested SQL
View as plain text  
>>>> 2012/09/10 15:49 -0700, Rick James >>>>
SELECT ... ORDER BY .. GROUP BY.. 
is syntactically incorrect.
<<<<<<<<
Yeap, my mistake.

>>>>>>>>
( 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'.
<<<<<<<<
But this behavior is guaranteed nor in MySQL:


11.16.3. GROUP BY and HAVING with Hidden Columns

MySQL extends the use of GROUP BY so that you can use nonaggregated columns or
calculations in the select list that do not appear in the GROUP BY clause. You can use
this feature to get better performance by avoiding unnecessary column sorting and
grouping. For example, you need not group on customer.name in the following query: 

SELECT order.custid, customer.name, MAX(payments)
  FROM order,customer
  WHERE order.custid = customer.custid
  GROUP BY order.custid;

In standard SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the
name is redundant. 

When using this feature, all rows in each group should have the same values for the
columns that are ommitted from the GROUP BY part. The server is free to return any value
from the group, so the results are indeterminate unless all values are the same. 

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