Re: is the "group by trick" valid?

Hi Rick,

Yes, the "group by trick" is safe to use in all currently released versions of 
MySQL. Note, however, that you are relying on undocumented and 
implementation-specific behavior that may change without warning in a future 
version of MySQL.

On 03/26/2012 11:55 PM, Rick James wrote:
> I would like validation that the "group by trick" is valid and stable. Could
> someone familiar with the code weigh in?
>
> The recurring problem is to find the latest (or largest, oldest, etc) value from
> each GROUP and furthermore show other columns associated with those groupwise
> MAX values.
>
> http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html
> explains the problem and provides classic solutions. In the comments, Speakman
> demonstrated an improved solution, and I chimed in, naming it the "group by
> trick" and pointing out that it was significantly faster for large datasets.
>
> Here is an example that boils it down to the essentials:
>
> Group-by trick example: Find the most populous city in
>   each state:
> [code]
> SELECT  state, city, population, COUNT(*) AS num_cities
>      FROM
>        ( SELECT  state, city, population
>              FROM  us
>              ORDER BY  state, population DESC ) p
>      GROUP BY  state
>      ORDER BY  state;
> +-------+-------------+------------+------------+
> | state | city        | population | num_cities |
> +-------+-------------+------------+------------+
> | AK    | Anchorage   |     276263 |         16 |
> | AL    | Birmingham  |     231621 |         58 |
> | AR    | Little Rock |     184217 |         40 |
> | AZ    | Phoenix     |    1428509 |         51 |
> | CA    | Los Angeles |    3877129 |        447 |
> ...
> [/code]
>
> In http://forums.mysql.com/read.php?20,521007,521709 there is a mild debate over
> the validity of the group by trick. It has worked flawlessly for me for many
> years (of course that does not prove its validity).
>
>
> --
> Rick James - MySQL Geek
>

-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals