Get the latest price of the latest date per item

Hello everyone,

I need some help. I have this cost_prices table and I need to get the highest and lowest price from the latest date per item.

-----------------------------------------
item_id  |  amount  | date
-----------------------------------------
1        | 100.00    |  2017-02-02
-----------------------------------------
1           | 50.00      |  2017-02-02
-----------------------------------------
1           | 75.00      |  2017-02-02
-----------------------------------------
1           | 70.00      |  2017-02-01
-----------------------------------------

I want the result to be

--------------------------------------------------
item_id  |  highest  | lowest  |  date
--------------------------------------------------
1         | 100.00    |  50.00  | 2017-02-02
---------------------------------------------------

Is there any way to achieve this. I've been stuck here for about 6hrs now. Thanks for you help.

tisuchi

9 years ago

Level 70

May be you can try query builders.

Table::min('highest')->max('highest')->groupBy('date')->get();

Ref: https://github.com/laravel/framework/blob/5.4/src/Illuminate/Database/Eloquent/Builder.php#L69-L72

Alternative

You may try this-

Table::select(DB::raw('id_zbozi, max(highest) as hid, min(lowest) as lid'))
                    ->groupBy('date')
                    ->get();

Please or to participate in this conversation.