Master\Detail Only Show Last Detail Record

[jamies]

Hi there,
I have 2 tables; Product_Master and Product_Details, whenever there is a change a new record is created within the Product_Details table.

Product_Master Looks Like (Product_ID,Product_Name,DateCreated,Verification, Drawing,Category)
Product_Details Looks Like (Product_Detail_ID,Product_ID,DateTimeStamp,Create dBy,Photograph,Location,Packaging,Current_Status)

I am needing a query that will pull out all The Products along with the last productDetail Created for that product.
A standard query would pull out all records on both sides so it would appear that the product is listed however many times it is mentioned in the ProductDetails Table.
In MSSQL I would use row_number() Over Partition and have it in a stored proc.

Any help would be great.
Thanks


Jamie

[rpeare]

Assuming your PRODUCT_DETAIL_ID is an autonumber and you can reliably determine that the most recent record is the one you're after you can create this query:

SELECT Product_ID, Max(Product_Detail_ID) as MRDetail_ID FROM Product_Details

This query will give you the most recent product_detail_id for any product, you can then link that back to your either of your main tables to pull the information you want.

[jamies]

Thanks, did the job.