Search Postgresql Archives

Re: Embarassing GROUP question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux