0

In my db i have stored price of products for everyday.

I need records based on last record of every month.

when i apply group by month, it is giving the first record of the month.

Query i have tried was, 

select * from product where product_id = 52   
AND YEAR(date_modified) = YEAR(NOW()) GROUP BY MONTH(date_modified)

Any ideas are welcome

Insane Skull
  • 9,220
  • 9
  • 44
  • 63
Arun
  • 146
  • 9
  • post your `create table code` please. – 1000111 Feb 11 '16 at 05:23
  • Last record of a month means highest last_modified value comparing with the other records' last_modified value of that month ? – 1000111 Feb 11 '16 at 05:24
  • OR last record of a month means that record will have the highest primary key value comparing with other records' primary key value of that month? – 1000111 Feb 11 '16 at 05:27
  • [Standard greatest-n-per-group-problem](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group). Your current query isn't actually getting the "first row for each month" - it's actually getting an **UNDEFINED** result row (could be _anything_), which happens to be the first of the month (likely due to probable indices). – Clockwork-Muse Feb 11 '16 at 05:32
  • Thanks guys for ur reply, I got solution by - select * from (select * from product ORDER BY date_modified DESC) as record where product_id = 52 and YEAR(date_modified) = YEAR(NOW()) GROUP BY month(date_modified) – Arun Feb 11 '16 at 05:40
  • thanks for reply yar – Arun Feb 11 '16 at 05:40
  • 1
    `select * from ( select * from product WHERE product_id = 52 ORDER BY date_modified DESC ) as record WHERE YEAR(record.date_modified) = YEAR(NOW()) GROUP BY month(record.date_modified)` it might give better performance comparing to the one stated above. – 1000111 Feb 11 '16 at 05:44
  • 1
    yeah correct yar. Just now our team was discussing about performance of the query, you replied finest query. superb move yar – Arun Feb 11 '16 at 05:54

1 Answers1

2

Have a derived table where you using GROUP BY find each product's latest date per month. Join with that derived table:

select * from
product t1
join (select product_id, max(date_modified) max_date_modified from product
      group by product_id, YEAR(date_modified), MONTH(date_modified)) t2
  on t1.product_id = t2.product_id and t1.date_modified = t2.max_date_modified
where product_id = 52
jarlh
  • 42,561
  • 8
  • 45
  • 63