Peter Beckman wrote:
On Mon, 13 Nov 2006, bluefx13@xxxxxxxxx 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/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
