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 <[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
