asked on

Maximum Record Selection

I have a table:

Product

This table is linked to another table:

Product Price

This table contains:
Price Date
Price

I need to be able to select the latest price for a product.

Mike McCracken

You are going to have to use a view/stored procedure in the database or a Crystal command

mlmcc

Mike McCracken

The query will be something like

SELECT Product.ID, Product.Description, ...
(SELECT Price.Price FROM Price WHERE Price.ProductID = Product.ID AND Price.PriceDate = Maximum(Price.PriceDate)
FROM Product

mlmcc

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James0628

FWIW, you _could_ find the most recent price in a report, but it would probably be much more efficient to do it in the db query, as mlmcc suggested. It would depend a lot on your data and the report, and what you wanted to do with that price (just display it, or use it in calculations).

James

whittinga2

ASKER

Cheers fellas. I will give it a go and get back to you.

Sorry for the delay.

whittinga2

ASKER

Any idea why I've been given a syntax error?

SELECT *
(SELECT * FROM pricehistory WHERE pricehistory.productid = productdetails.productid AND pricehistory.priceeffectivefromdate =
(SELECT Maximum(pricehistory.priceeffectivefromdate) FROM pricehistory WHERE pricehistory.productid = productdetails.productid))
FROM productdetails

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

whittinga2

ASKER

Thanks to both of you. Points allocated.