Problems with group by

Querying not working as expected

[chrisjoha]

Hi, so I've discovered that a query I thought was working fine is not at all working fine... Hope you guys can help me. The example is simplified for clarification. Hope it's clear enough. Basically I have a table like this:

items
-----
itemid (int), price (float), pricedate (timestamp), field1, field2, aso

and I want to select the price and date of each item - one price/date for each item each month - more specifically the price and date each month the day the price is the lowest. So I though I'd go for a grouped query and did:

SELECT min(price) as minprice, pricedate, item
FROM items
GROUP BY date_format(pricedate, '%Y%m')

And I thought that was it - it gives me the following:

item ¦ price ¦ date 
------------------------- 
1  ¦ 100  ¦ 2005-10-01 
1  ¦ 69  ¦ 2005-11-02 
2  ¦ 765  ¦ 2005-10-01 
2  ¦ 45  ¦ 2005-11-01 

And so on. The price is indeed the lowest one - only the date is wrong (also, any other fields I include in the SELECT part seems to be randomly chosen - not the actual ones from the row with the lowest price). It just selects the first available date (day) each month.

How can I make this query select fields that actually belong to the row with the lowest price? Any help would be greatly appreciated, this is toying with my SQL competance level... :)

[chrisjoha]

Sorry to bump my own thread, but has anyone got any ideas? I've done alot of googling, but I cannot find anyone that offers a suitable example, they're all "SELECT sum(something), user FROM some_action GROUP BY user" :)

[coopster]

It's the "Single-Value Rule". Perhaps this thread will help ...

[webmasterworld.com...]

[arran]

There's also some invaluable advice on sock selection strategies in that thread :)

[chrisjoha]

So basically what I'm trying to do isn't possible in one go? Well, gosh darnit... I have now installed MySQL5 in hopes that creating some views will help me solve this problem without insane amounts of queries. Does anyone have some tips on hoe I should make this whole thing into an effective set of queries?

[Dijkgraaf]

re-read the question, decided to remove my answer

[syber]

You need to use a subquery for detail information.

It would look something like this:

SELECT item, price as minprice, pricedate 
 FROM items AS i1 
 WHERE price = (SELECT MIN(price)  
     FROM items AS i2 
     WHERE i1.item = i2.item AND  
     date_format(i1.pricedate, '%Y%m') =  
     date_format(i2.pricedate, '%Y%m') 
    ) 
 ORDER BY 1,2,3  

[chrisjoha]

Thanks for your suggestion. Unfortunately a query like that spent 40-48 seconds executing. A realistic number of result rows between 3-400 took 43 seconds, and that's way too long :( Can I optimize it in any way?

[syber]

Try creating a compound index on item, price, pricedate

[chrisjoha]

I worked this out by changing to MySQL 5, creating a view with the month prices and then performed a join on the view with the original table to get the full rows. It works like a charm. Thank you alot for your help!