Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
How to retrieve latest production date for each repair order
Versions des environnements
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement