0

I have two tables
1: itemInfo

product_id  brand   name    category 
1   Garden  Goodcake    cake

2: itemRecord

record_id shopType price inputTime product_id       
1   0   7.2     2012-03-20 17:58:56 1
69  2   6.4     2012-03-20 19:33:12 1
70  0   9.9     2012-03-19 00:00:00 1
71  3   5.5     2012-03-18 05:22:00 1
72  0   6.8     2012-03-19 20:26:29 1
73  0   10.1    2012-03-17 12:27:00 1
74  3   7.9     2012-03-16 00:00:00 1
75  1   6.7     2012-03-15 00:00:00 1
76  1   8.8     2012-03-14 11:24:00 1
77  2   12.5    2012-03-13 10:26:19 1
78  0   7.2     2012-03-21 11:25:23 1   
146 2   5       2012-03-21 12:53:04 1
149 3   4.9     2012-03-21 10:00:00 1
150 3   5.1     2012-03-21 12:20:00 1

I would like to display the latest 7 days of cheapest price of product_id = 1. If the same day, the cheapest price is same, just display the latest.
The final table should be: 

product_id brand   name     category inputTime  shopType minimum_price record_id
1          Garden  Goodcake cake     2012-03-21 3        4.9           149
1          Garden  Goodcake cake     2012-03-20 2        6.4           69
1          Garden  Goodcake cake     2012-03-19 2        6.8           72
...
1          Garden  Goodcake cake     2012-03-15 1        6.7           75

I have tried my best to write a sql that like: 

SELECT r.product_id, i.brand, i.name, i.category, DATE_FORMAT( r.inputTime, '%e-%b' ) AS inputTime, r.shopType, MIN( r.price ) AS minimum_price, r.record_id
FROM itemRecord AS r, itemInfo AS i
WHERE r.product_id = '1'
AND i.id = r.product_id
GROUP BY DATE( r.inputTime ) DESC
LIMIT 0 , 7

However, the table is: 

product_id  brand   name    category    inputTime   shopType    minimum_price   record_id
1   Garden  Goodcake cake       21-Mar  0   4.9     78
1   Garden  Goodcake cake       20-Mar  0   6.4     1
1   Garden  Goodcake cake       19-Mar  0   6.8     70
1   Garden  Goodcake cake       18-Mar  3   5.5     71
1   Garden  Goodcake cake       17-Mar  0   10.1    73
1   Garden  Goodcake cake       16-Mar  3   7.9     74
1   Garden  Goodcake cake       15-Mar  1   6.7     75

For example, the first row result, it displays the correct minimum price 4.9, but the record_id is wrong, also, the shopType is wrong too.
I have research a lot, but still cannot solve the problem.
Please help...

red23jordan
  • 2,841
  • 10
  • 41
  • 57

1 Answers1

2

JOIN them:

SELECT r.product_id, i.brand, i.name, i.category, DATE_FORMAT( r.inputTime, '%e-%b' ) AS inputTime, r.shopType, r.price AS minimum_price, r.record_id
FROM (  SELECT *
        FROM itemRecord
        WHERE product_id = '1'
        ORDER BY price ASC, inputTime DESC) AS r
INNER JOIN itemInfo As i
ON r.product_id = i.product_id
WHERE i.id = r.product_id
GROUP BY DATE(r.inputTime)
LIMIT 0, 7

Explanation:

I do an inner query which renders the table order by price ASC instead of the default, lets say id ASC. When you GROUP BY the rows, it uses the columns from the first row by default, which in this case is the one with lowest price.

Your solution didn't work since it might aswell have chosen the first id and not the lowest price row. The only column which was correct was the MIN( r.price ), but as you noticed that function did not affect the other columns in the result.

Robin Castlin
  • 10,956
  • 1
  • 28
  • 44
  • sorry, the result stills wrong, the same result as above I posted – red23jordan Mar 21 '12 at 09:18
  • can you explain more why you solve in this way?? Thx very much – red23jordan Mar 21 '12 at 09:25
  • This approach is considered unreliable and should not be used. If you decide to use it anyway, then should at least apply the relevant criteria to the formation of the derived table as well as the outer table `WHERE product_id = 1`. I would also suggest using `DATE(r.inputTime)` as it should be more efficient than grouping on the string returned from `LEFT()`. – user1191247 Mar 21 '12 at 12:56
  • Why would DATE() be more efficient? I don't see why that function should be faster then a simple substr(). I'm well aware of it being considered unreliable, but as long as you keep in mind that the first rows in the inner query is the data you're after, I've yet to seen it give wrong data, thus making it reliable for me. – Robin Castlin Mar 21 '12 at 13:14
  • @RobinCastlin - fair comment. I have spent the last two hours trying to validate the claim that it is unreliable. It seems that it is related to it being undocumented functionality of the internals (I have used it in the past without any problems). You need to add the `inputTime DESC` to the ordering of your inner query to make sure it returns the latest record in the case that there is more than one record with min price. `DATE()` is more efficient (about 30%) for the ordering. I am not sure why but I assume it relates to the internal handling of the DATE type. – user1191247 Mar 21 '12 at 15:07
  • The other point about adding the WHERE clause to the inner query is more important, as without it the derived table will contain every record. If the table is expected to be very large I would also filter the results of the inner query based on date. – user1191247 Mar 21 '12 at 15:10
  • Yeah, I've considering filtering the inner query aswell, however I do wonder if giving the entire table's content in simply an other order really is any worse then doing the same, but filtering aswell. I haven't noticed much lag when I select a table in a different order without calculations included. The reason I prefer LEFT() over date is because it makes it easy to filter YEAR, MONTH and so on by simply changing the last number. :) – Robin Castlin Mar 21 '12 at 15:24
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/9149/discussion-between-nnichols-and-robin-castlin) – user1191247 Mar 21 '12 at 15:31