>Hi,
>
>I have a table that contains the following fields:
>production_Item_Id (autoinecrement)
>lookup_key (numeric)
>order_number (numeric)
>item_date (date())
>
>Each vehicle repair order might go through 10 production steps. As the vehicle enters for example the body shop they enter the date it went in. The lookup_key corresponds to a field in a lookup table that contains the name of the production step (body shop, mechanic, etc.) The date for each step on a repair order will be unique. As they enter a step, it is appended to the table.
>
>I'm trying to retrieve the latest(lookup_key, order_number and item_date) for a report. If I have only 2 repair orders in the system, I should get only the two latest production items. I've tried the max aggregate on item_date but that only works if I only retrieve 2 fields (order_number, item_date). Add the third field (now you have to group by lookup_key and order_number) and you get all the rows again. I've tried DISTINCT, self joins but can't get it to work.
>
>Any suggestions?
>
>Thanks,
>

Try

select * from Orders where production_Item_Id IN ;  
  	(SELECT CTOBIN(RIGHT(MAX(DTOS(item_date )+BINTOC(production_Item_Id )),4)) ;  
  			FROM Orders GROUP BY order_number)

based on the idea by Igor Korolev from http://forum.foxclub.ru/read.php?32,177183,177232#msg-177232

See also
http://www.tek-tips.com/viewthread.cfm?qid=1367284&page=1