MYSQL Time difference between MAX and MIN from single table

[Matrixkid]

Hi there,

 

I have a table as outlined below and I'm trying to get the MAX and MIN values for a specific Id but only if the MAX entry date is greater than the MIN entry date.

 

table:

ID  ENTRY_DATE              NAME       PRICE
1   2012-01-23 16:09:35     MONKEY     99.33
2   2012-01-23 11:04:09     MONKEY     97.65
3   2012-01-23 09:31:19     MONKEY     93.05
4   2012-01-23 15:12:14     DICE       30.49
5   2012-01-23 12:01:24     DICE       32.00
6   2012-01-23 08:01:24     DICE       31.00

 

So what im trying to do is grab the MAX(price) and MIN(price) when the MAX(price) entry_date is greater than the MIN(price) entry_date. From the table above, my result would be:

 

NAME      MaxPrice     MinPrice   PriceDiff   
MONKEY    99.33        93.05      6.28

 

'Dice' would not show up because the MAX date/time happens before the MIN.

 

I have tried a LEFT JOIN but I cant seem to figure it out. Here is what I have right now, but it obviously grabs all results as I cant seem to figure out how to compare the entry dates...

 

SELECT name,
MAX(price) as MaxPrice,
MIN(price) as MinPrice,
MAX(price)-MIN(price) AS PriceDiff,
FROM products
WHERE DATE(entry_date) = DATE(NOW()) 
GROUP BY name
ORDER BY PriceDiff DESC

 

Thanks for the help!

 

[kickstart]

Hi

 

Think you will need to do a JOIN back from a subselect which gets the max and min prices.

 

Something like this:-

 

SELECT Sub1.name, Sub1.MaxPrice, Sub1.MinPrice, (Sub1.MaxPrice -Sub1.MinPrice) AS PriceDiff
FROM (SELECT name, DATE(entry_date) AS EntryJustDate, MAX(price) AS MaxPrice, MIN(price) AS MinPrice
FROM products
GROUP BY name, DATE(entry_date)) Sub1
INNER JOIN products products1 ON Sub1.name = products1.name AND Sub1.EntryJustDate = DATE(products1.entry_date) AND sub1.MaxPrice = products1.price
INNER JOIN products products2 ON Sub1.name = products2.name AND Sub1.EntryJustDate = DATE(products2.entry_date) AND sub1.MinPrice = products2.price
WHERE products1.entry_date > products2.entry_date
AND Sub1.EntryJustDate = DATE(NOW())

 

Note that isn't tested.

 

Also not that it will bring back some duplicate values if the max or min prices are not unique on a day (can be coded out but adds more complexity and not sure if this is an issue for you).

 

All the best

 

Keith