I have a table like this:
id product_id price date
1 1001 100 2017-05-29
2 1005 101 2017-03-30
3 1001 102 2017-03-30
4 1003 106 2017-06-07
5 1005 106 2017-04-30
I want to group by product_id and get only one latest product for each product_id. I also have a min and max price variable which should be applied to the price column.
the result of my query should be:
1 1001 100 2017-05-29
4 1003 106 2017-06-07
5 1005 106 2017-04-30
Is it possible by SQL or I should use application side?
If you want to order results by date, just use orderBy():
Model::orderBy('date_column', 'desc')->whereBetween('price', [$min, $max])->groupBy('product_id')->take(3)->get();
Or it's shortcuts latest() and oldest():
Model::latest('date_column')->...
You can use subqueries. First select from your table order by date, then use that to group by product_id:
SELECT * FROM (SELECT * FROM `your_table` t ORDER by date desc) as date_ordered group by product_id ORDER by id