Hi Mr. Jerry, Since Mysql does not support sub query, you will have to use temporary table instead.
Create temporary table tmptbl1 Select keycol, Max(datetimecol) as maxdate from detailtable group by Keycol; Create temporary table tmptbl2 Select distinct T.Keycol, D.Status from tmptbl1 T, detailtable D Where T.Keycol = D.keycol and T.maxdate = D.datetimecol; Select M.*, T2.Status From mastertable M, tmptbl2 T2 Where M.Keycol = T2.Keycol; I think this is the most straight forward way to solve your problem. I have not checked the above commands. Hope this works for you. Anvar. At 02:41 PM 02/11/2001 +1100, you wrote: >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. --------------------------------------------------------------------- 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
