Corey Tisdale <corey@xxxxxxxxxxxxxxxx> writes: > We're coming from mysql 4, and changing the sort order changes the > values of all columns as you would expect, given that you would expect > a sort statement to affect grouping. This certainly isn't the only > time I've used this syntax. I've been mysql user for ten years, and > the outcome has been consistant across hundreds of tables and millions > of rows and thousands of queries. If you ever have to use or modify a > mysql db, just keep this in mind in case it saves you some time. Okay, I got sufficiently interested to drag out the nearest copy of mysql and try it ... mysql> create table t (f1 int, f2 int, f3 int); Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(1,11,111), (1,22,222), (1,44,444), (1,33,333); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t values(2,55,555), (2,22,222), (2,44,444), (2,33,333); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t values(3,55,555), (3,22,222), (3,44,444), (3,77,777); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t group by f1 order by f2; +------+------+------+ | f1 | f2 | f3 | +------+------+------+ | 1 | 11 | 111 | | 2 | 55 | 555 | | 3 | 55 | 555 | +------+------+------+ 3 rows in set (0.00 sec) mysql> select * from t group by f1 order by f2 desc; +------+------+------+ | f1 | f2 | f3 | +------+------+------+ | 2 | 55 | 555 | | 3 | 55 | 555 | | 1 | 11 | 111 | +------+------+------+ 3 rows in set (0.00 sec) Looks to me like we're arbitrarily getting the physically-first row in each f1 group. It's certainly not looking for the minimum or maximum f2. The above is with 5.1.37, but I find essentially the same wording in the 3.x/4.x manual as in the 5.1 manual. Now it's certainly possible that in particular circumstances you might happen to get the right results --- for example, a scan that was using an index might happen to deliver the rows in the right order. But I don't see any evidence that mysql is reliably producing groupwise minimums or maximums with this syntax. The long discussions in the comments here: http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html don't suggest that anyone else believes it works, either. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
