Hi again!
We're getting a bit of topic here, since this is pure SQL.. But anyway...
I've played around with this one a bit since it seemed quite
interesting... The best I can do is to get the oldest action...
select TableA.record_id, product_ref, action, time_stamp from TableA
join TableB on TableA.record_id = TableB.record_id group by record_id;
Here's the test data:
mysql> select TableA.record_id, product_ref, action, time_stamp from
TableA join TableB on TableA.record_id = TableB.record_id;
+-----------+-------------+--------+----------------+
| record_id | product_ref | action | time_stamp |
+-----------+-------------+--------+----------------+
| 1 | 100 | A | 20080306220037 |
| 1 | 100 | C | 20080306220041 |
| 1 | 100 | E | 20080306220045 |
| 2 | 102 | A | 20080306220052 |
| 3 | 110 | A | 20080306220055 |
| 3 | 110 | E | 20080306220058 |
| 4 | 120 | B | 20080306220105 |
| 4 | 120 | C | 20080306220109 |
+-----------+-------------+--------+----------------+
And with the query above we get the opposite of the desired behavior,
the oldest action (if that's the order in the database):
mysql> select TableA.record_id, product_ref, action, time_stamp from
TableA join TableB on TableA.record_id = TableB.record_id
group by record_id;
+-----------+-------------+--------+----------------+
| record_id | product_ref | action | time_stamp |
+-----------+-------------+--------+----------------+
| 1 | 100 | A | 20080306220037 |
| 2 | 102 | A | 20080306220052 |
| 3 | 110 | A | 20080306220055 |
| 4 | 120 | B | 20080306220105 |
+-----------+-------------+--------+----------------+
4 rows in set (0.00 sec)
Now is the question: Does anyone know how to get the 'group by' clause
to leave a specific row 'visible' at top? Like the last inserted or by
the order of another column...
Since MySQL 4.1 there are also a GROUP_CONCAT() function that can
concatenate multiple 'rows' to a string in a desired order, but it does
not support the limit statement... so that won't help us much I think.
We can get all the actions in a string with the newest first, but then
some post-stripping of the data is needed.
It seems like you need to do this with two queries in PHP, if no one has
an answer to the question stated above. You can always buffer your
result in an array in PHP and do whatever sorting you want to before
using your data...
With the MAX() function we can found out when the last action was made,
but we get the wrong action with the correct time:
mysql> select TableA.record_id, product_ref, action, max(time_stamp)
from TableA join TableB on TableA.record_id = TableB.record_id
group by record_id;
+-----------+-------------+--------+-----------------+
| record_id | product_ref | action | max(time_stamp) |
+-----------+-------------+--------+-----------------+
| 1 | 100 | A | 20080306220045 |
| 2 | 102 | A | 20080306220052 |
| 3 | 110 | A | 20080306220058 |
| 4 | 120 | B | 20080306220109 |
+-----------+-------------+--------+-----------------+
4 rows in set (0.00 sec)
Hmm... Now I'm stuck! :)
Greetings,
Krister Karlström, Helsinki, Finland
Graham Cossey 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.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
