Thanks for your reply, but my problem is not solved by your suggestions: > mysql>> select *,max(cb) from testb left join testa using (ca) group by > > testb.ca; > 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:
OK, you are right, but thats not what i meant: > 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 | > +------+----------------+---------+ What I need to get is +------+----------------+------+------+------+ | ca | time | ca | cb | mm | +------+----------------+------+------+------+ | 2 | 20020705145347 | 2 | 9 | 9 | | 3 | 20020705145349 | 3 | 3 | 3 | +------+----------------+------+------+------+ so i want to get the records of testa, that are also listed in testb (ca-column), and have the maximum cb-value out of all records with the same ca-value. and it's not the max()-value that helps me instead of the cb-value; My tables have much more columns filled with data and i have to get mysql to use the record with the max()-value when grouping the records so i get the right other data-columns of testa, too. i want to get following: ca cb 2 9 3 3 out of testa to be the result. i guess i have to use a temporary table... :( Richard -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net --------------------------------------------------------------------- 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
