Hi,
=== Question No.1 === mysql>> select *,max(cb) from testb left join testa using (ca) group by > testb.ca; > +------+----------------+------+------+---------+ > | ca | time | ca | cb | max(cb) | > +------+----------------+------+------+---------+ > | 2 | 20020705145347 | 2 | 2 | 9 | > | 3 | 20020705145349 | 3 | 3 | 3 | > +------+----------------+------+------+---------+ This query selects all the fields from testb (ca, time) AND testa (ca, cb) because of the LEFT JOIN you used. You should try specifying the just fields you need: mysql> select testb.ca, testb.time, max(cb) from testb left join testa using (ca ) group by testa.ca; +------+----------------+---------+ | ca | time | max(cb) | +------+----------------+---------+ | 2 | 20020705155526 | 9 | | 3 | 20020705155534 | 3 | +------+----------------+---------+ 2 rows in set (0.01 sec) === Question No.2 === mysql>> select *,max(cb) as mm from testb left join testa using (ca) where > testa.cb=mm group by testb.ca; > ERROR 1054: Unknown column 'mm' in 'where clause' mysql>> select *,max(cb) as mm from testb left join testa using (ca) where > testa.cb=max(cb) group by testb.ca; > ERROR 1111: Invalid use of group function You should be using the HAVING clause, linke this: mysql> select testb.ca, testb.time, max(cb) from testb left join testa using (ca ) group by testa.ca having testb.ca=max(cb); +------+----------------+---------+ | ca | time | max(cb) | +------+----------------+---------+ | 3 | 20020705155534 | 3 | +------+----------------+---------+ 1 row in set (0.00 sec) Regards, Sasa »mysql, select, database« --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
