Gimali Gimali - 7 months ago 19
SQL Question

mysql get last N records with MAX(date)

So I have following data in a product_rate_history table -
enter image description here

I want to select last N records ( eg 7 records ) informing rate change history of given product. If product rate is changed more than one time a day, then query should select most recent rate change for that day.

So from above table I want output like following for product id 16-

+-----------+-------------------------+------------------------+
| product_id | previous_rate | date |
+----------------+--------------------+------------------------|
| 16 | 2400 | 2016-04-30 23:05:35 |
| 16 | 4500 | 2016-04-29 11:02:42 |
+----------------+--------------------+------------------------+


I have tried following query but it returns only one row having last update rate only-

SELECT * FROM `product_rate_history` prh
INNER JOIN (SELECT max(created_on) as max FROM `product_rate_history` GROUP BY Date(created_on)) prh2
ON prh.created_on = prh2.max
WHERE prh.product_id = 16
GROUP BY DATE(prh.created_on)
ORDER BY prh.created_on DESC;

Answer

First, you do not need an aggregation in the outer query.

Second, you need to repeat the WHERE clause in the subquery (for the method you are using):

SELECT prh.*
FROM product_rate_history prh INNER JOIN
     (SELECT max(created_on) as maxco
      FROM product_rate_history
      prh.product_id = 16
      GROUP BY Date(created_on)
     ) prh2
     ON prh.created_on = prh2.maxco
WHERE prh.product_id = 16
ORDER BY prh.created_on DESC;
Comments