Mysql doesn't support subselects in 4.0.x. That was added in 4.1. -Roberto J. Hill wrote: > I may be a little confused: the desire is to return all the rows from > TableA that match the record_id of a row in TableB that has the MAX > timestamp? > > If so, why not something like: > > SELECT * FROM TableA a, TableB b WHERE a.record_id=b.record_id && > timestamp=(SELECT MAX(timestamp) FROM TableB) ORDER BY action; > > I'm guessing I'm confused, that it's something more complicated you're > looking for. > > Jeff > > > Krister Karlström wrote: >> 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 <graham.cossey@xxxxxxxxx> >>> 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
