This will give you: ERROR 1111: Invalid use of group function
It seems like the use of an aggregate (or how is it spelled?) function is not allowed in a join statement...
/Krister Jon L. wrote:
You can try adding a quick test to the ON statement... SELECT * FROM TableA INNER JOIN TableB ON TableA.record_id = TableB.record_id AND TableB.timestamp = MAX(TableB.timestamp) Now, I haven't tested it. I can only say the theory of it is accurate. - Jon L. On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey <[EMAIL PROTECTED]> wrote:I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : "SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1" I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
