SQL "GROUP BY" issue

Question

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

Answer 1

You might wanna try this:

SELECT pid, price, date FROM ProductPrice GROUP BY pid ORDER BY date DESC

Group has some obscure functionality, I'm too always unsure if it's the right field...but it should be the first in the resultset.

Bobby

Answer 2

I think you broke your database schema.

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.

As you have pointed out you need to keep a change history of prices. But you can still keep the current price in the products table in addition to that new table. That would make your life much easier (and your queries faster).

Answer 3

The reason you get an arbitrary price is that mysql cannot know which columns to select if you GROUP BY something. It knows it needs a price and a date per pid and can fetch the latest date as you requested with max(date) but chooses to return a price that is most efficient for him to retrieve - you didn't provide an aggregate function for that column (your first query is not valid SQL, actually.)

Your second query looks OK, but here is a shorter alternative:

SELECT pid, price, date
FROM ProductPrice p
WHERE date = (SELECT MAX(date) FROM ProductPrice tmp WHERE tmp.pid = p.pid)

But if you access the latest price a lot (which I think you do), I would recommend adding the old column back to your original table to hold the newest value, if you have the option of altering the database structure again.

Answer 4

Here is another -possibly inefficient- one:

SELECT pid, substring_index( group_concat( price order by date desc ), ',', 1 ) , max(date)
  FROM ProductPrice
GROUP BY pid

Answer 5

I think that the key here is simple sounding query - you can see what you want but computers ain't human and so to produce the desired result from set based operations you have to be explicit as in the second query.

The inner query identifies the last price for each product, then the outer query lets you get the value for the last price - that's about as simple as it can get.

As an aside, if you have an invoicing system, you really ought to store the price for the product (and the tax rates as well as the "codes") with the invoice i.e. the invoice tables should contain all the necessary financial information to reproduce the invoice. In general, you do not want to rely on being able to look up a price (or a tax rate) in a mutable table even allowing for the system introduced as above. Regardless of this have the pricing history has its own merits.

Answer 6

You cannot solve your problem with the GROUP BY clause, because for each group of pid MySQL will simply fetch the first pid, the maximum date and the first price found (which is not what you need).

You may either use a subquery (which can be inefficient):

SELECT pid, date, price
FROM   ProductPrice p1
WHERE  date = ( SELECT MAX(p2.date)
                FROM ProductPrice p2
                WHERE p1.pid = p2.pid)

or you can simply join the table with itself:

SELECT    p1.pid, p1.date, p1.price
FROM      ProductPrice p1
LEFT JOIN ProductPrice p2 ON p1.pid = p2.pid
          AND p1.date < p2.date
WHERE     p2.pid IS NULL

Take a look at this section of MySQL docs.

Answer 7

i faced same problem in one of my project i used subquery to fetch date and then compare it but it makes system slow when data increases. so, its better to store latest price in your Products table in addition to the new table you have created to keep history of price changes.

you can always use any of query ppl suggested to get latest price of product on particular date. but also you can add one field in the same table is it latest. so for one date you can make flag true once. and you can always find product's latest price for particular date by one simple query.