Re: Help with JOIN query

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.