>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,
>
>Marcel

select * from myTable t1 ;
 where item_date = ;
 (select max(item_date) from myTable t2 ;
  where t1.order_number = t2.order_number)

Cetin