How to display the current price of the goods if they change every day?

Question

MySQL

Hi friends!
There is a product table sku:
id, sku, name
and there is a price table sku_costs:
id, sum, datefrom
Where datefrom is the date the price was changed and sum is the new price:

2,500,2018-02-01 10:00:00
2,550,2018-02-05 18:00:00

(i.e. from this example it is clear that the current price is 550 rubles for item #2)
Question:
How to display a list of items with current prices at the moment?
I try like this:

select s.*, sc.`sum`, max(sc.datefrom) from sku s
left join sku_costs sc
  on s.id = sc.id 
group by s.id

and of course it doesn't work that way.
Please help! Thank you.

Answer 1

SELECT
 s.`id`,
 (select sc.`sum` FROM `sku_costs` sc WHERE sc.`id`=s.`id` ORDER BY sc.`datefrom` DESC LIMIT 1) AS `price`
FROM `sku` s

add PK (id, datefrom DESC) to sku_costs
when the request starts to slow down - denormalize the database, store the current price as a separate field in sku and update when the price changes, and use sku_costs as a price archive