[thelist] MySQL Grouping problem

[Joshua Olson]

> -----Original Message-----
> From: Phil Turmel
> Sent: Monday, April 11, 2005 5:06 PM
> 
> Richard/Joshua:
> 
> MySQL has an example for this problem that addresses pre-4.1 
> and current 
> syntax (just max instead of min, adjust to suit):
> 
> http://dev.mysql.com/doc/mysql/en/example-maximum-column-group
> -row.html

This solution, which uses temporary tables, is a viable solution.  On a
similar note, this technique also can be used when creating high-speed
pagination across huge recordsets.

> As for fields in the select list that are neither aggregate 
> nor grouped, 
> MySQL extends the standard to pick ANY value from the grouped rows:
> 
> http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html

This was the oddity to which I was referring.  We had a discussion about
this a few years back as well.  Per the article:

"Do not use this feature if the columns you omit from the GROUP BY part are
not unique in the group! You get unpredictable results. "

Er, yeah, I can see why.  That is why most most SQL implementations don't
allow for it.  :-)

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168