[thelist] MAX and GROUP BY question

r937 rudy at r937.com
Wed Oct 24 20:19:50 CDT 2007

for more on mysql's behaviour, see

  Debunking GROUP BY myths

i don't have a few hunnert dollars to toss around, so i don't have a copy of
the sql standard mysqlf and cannot comment with authority on it, but
according to this article,

  "As of the 1999 version of the SQL standard, it is explicitly allowed
   for the SELECT list to reference non-aggregated expressions
   as long as they are functionally dependent upon the GROUP BY list."

in noah's query, status is not functionally dependent on client_id, and
therefore is indeterminate (and the query is invalid)

i think it's a great standard, and makes lots of sense, because it lets you
write queries such as

   select cust_id, cust_name, cust_address, count(order_id) ...
       group by cust_id

without having to repeat all the functionally dependent customer columns in

and in mysql, it even executes faster


More information about the thelist mailing list