How to select the last rows from the price table by product id?

Question

SQL

Please help with a request.
There is a price table.
There are 4 fields (id, price, product_id, timestamp)
The meaning is that when the price changes, the old prices should be stored in the table for other needs.
And as a result, for each product there are several lines with prices tied to the product_id field.
And I need to display for each product only the last price by the time it was added.
The basic request is:

'SELECT product.*, prices.price FROM  product  LEFT JOIN prices 
         ON  prices.product_id = product.id 
         GROUP BY product.id'

Answer 1

Using window functions ROW_NUMBER :

SELECT * FROM (
  SELECT 
    product.*, 
    prices.price ,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY timestamp DESC) rn
  FROM  product 
  JOIN prices ON  prices.product_id = product.id 
) prices WHERE rn = 1
;

mariadb fiddle