I often find myself in the following situation:
I have a master table and a detail table, the detail table contains chronologically ordered items (statuses) that relate to the master record. I now need to display one line for each of the master records with the latest status from the detail table. I have experimented with the GROUP BY and ORDER BY clauses of MySQL, but it seems to have a mind of its own with regard to which detail record gets returned in the collapsed result set. Is there any way I can influence the record in a secondary table that is returned as a result of grouping on a primary table? I tried selecting a MAX on the timestamp field, in the hope that somehow the resolution of that maximum would leave the other fields in the record sticky to the position that the maximum was found... with no success... I really would like to avoid changing the structure of all my database tables to contain the latest statuses in the master table. For one thing this clutters up the database design by replicating information. Jerry van Leeuwen Business Analyst - Trans Data Tel: 02 - 9630 3533 Mobile: 0407 - 480 811 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
