Re: MySQL SQL Query Help

Peter Beckman wrote:

On Mon, 13 Nov 2006, bluefx13@gmail.com wrote:

Actually, that should not work, it should give you an error. This should work: SELECT fkid,max(foo) as foo,bar FROM test2 GROUP BY fkid ORDER BY bar ASC

Yes, but if the data is in a different order that fails and doesn't maintain row order:

    mysql> create temporary table test2 (id tinyint,fkid tinyint, foo smallint, bar varchar(20));
    mysql> insert into test2 values (1,1,34,'red'), (2,1,345,'yellow'), (3,2,345,'brown'), (4,2,3459,'green');
    mysql> select * from test2;
    +------+------+------+--------+
    | id   | fkid | foo  | bar    |
    +------+------+------+--------+
    |    1 |    1 |   34 | red    |
    |    2 |    1 |  345 | yellow |
    |    3 |    2 |  345 | brown  |
    |    4 |    2 | 3459 | green  |
    +------+------+------+--------+
    mysql> SELECT fkid,max(foo) as foo,bar FROM test2 GROUP BY fkid  ORDER BY bar ASC;
    +------+------+-------+
    | fkid | foo  | bar   |
    +------+------+-------+
    |    2 | 3459 | brown |
    |    1 |  345 | red   |
    +------+------+-------+
    2 rows in set (0.00 sec)

Notice how 3459 is supposed to be green but reports brown, and 345 should be yellow but reports red? Any other solutions that maintain row integrity?

You might have to go a subquery. (I'm not great at subqueries so there would have to be a better way to write this anyway). This works in postgresql: select fkid, foo, bar from test2 t2 where (select max(foo) from test2 t1 where t1.fkid=t2.fkid)=foo; but mysql won't let you reference the same table inside & outside: mysql> select fkid, foo, bar from test2 t2 where (select max(foo) from test2 t1 where t1.fkid=t2.fkid)=foo; ERROR 1137 (HY000): Can't reopen table: 't2' http://dev.mysql.com/doc/refman/5.0/en/subqueries.html The mysql mailing list might have better ideas.. -- Postgresql & php tutorials http://www.designmagick.com/