Hi,
I'm designing a shopping cart. To circumvent the problem of old invoices showing inaccurate pricing after a product's price gets changed, I moved the price field from the Product table into a ProductPrice table that consists of 3 fields, pid, date and price. pid and date form the primary key for the table. Here's an example of what the table looks like:
pid date price
1 1/1/09 50
1 2/1/09 55
1 3/1/09 54
Using SELECT
and GROUP BY
to find the latest price of each product, I came up with:
SELECT pid, price, max(date) FROM ProductPrice GROUP BY pid
The date and pid returned were accurate. I received exactly 1 entry for every unique pid and the date that accompanied it was the latest date for that pid. However, what came as a surprise was the price returned. It returned the price of the first row matching the pid, which in this case was 50.
After reworking my statement, I came up with this:
SELECT pp.pid, pp.price, pp.date FROM ProductPrice AS pp
INNER JOIN (
SELECT pid AS lastPid, max(date) AS lastDate FROM ProductPrice GROUP BY pid
) AS m
ON pp.pid = lastPid AND pp.date = lastDate
While the reworked statement now yields the correct price(54), it seems incredible that such a simple sounding query would require an inner join to execute. My question is, is my second statement the easiest way to accomplish what I need to do? Or am I missing something here? Thanks in advance!
James