Mysql load the last purchase of each item

Question

I can a list of technical sheets of some products where I need to make the cost calculation taking into account only the last purchases made of each item of a technical sheet.

For example, to make a cake I will use the items according to the following data sheet:

tb_ficha
id | produto | custo_total
1  | Bolo    | 10.25

tb_ficha_produto
id | id_ficha | id_produto | quantidade
1  | 1        | 7 (trigo)  | 0.400
2  | 1        | 8 (ovos)   | 2.000
3  | 1        | 9 (sal)    | 0.010

The organization of purchases follows the following tables::

tb_compra
id | data_compra | valor_produtos | valor_entrega

tb_compra_produto
id | id_compra | id_produto | quantidade | valor

And the table that binds everything, which is that of each individual product:

tb_produto
id | nome   | materia_prima...
1  | Trigo  | 1
2  | Álcool | 0

To query the following is returning me almost correctly the results. I can filter and get the list of id and valor only the products that belong to the data sheet. However, if there were 10 purchases of the product, 10 results will appear in the query, being that I only need the last record. Recalling that I must take into consideration the field data_compra, because purchases are not recorded in sequential order.

SELECT a.id_produto, a.valor_unitario, b.data_compra

FROM tb_compra_produto a

INNER JOIN tb_compra b
    ON b.id = a.id_compra

INNER JOIN tb_ficha_produto c
    ON c.id_produto = a.id_produto

INNER JOIN tb_ficha d
    ON d.id = :id_ficha //recebo de uma variável
    AND d.id = c.id_ficha

How can I adjust to query to return only the desired result? Or what other method can I use to get the same result?

--

Resultados obtidos:
id_produto | valor | data
3          | 14.89 | 2019-10-12
5          | 07.90 | 2019-10-09
5          | 08.90 | 2019-10-01
3          | 13.90 | 2019-10-09
5          | 08.90 | 2019-09-28
3          | 14.80 | 2019-09-27
9          | 09.90 | 2019-10-10
9          | 09.90 | 2019-09-07
5          | 07.99 | 2019-09-23
3          | 14.80 | 2019-09-25
9          | 10.90 | 2019-09-30

Resultados desejados:
id_produto  | valor | data
3           | 14.89 | 2019-10-12
5           | 07.90 | 2019-10-09
9           | 10.90 | 2019-10-10

Answer 1

One way to solve this would be to do a query to return the last purchase ID of each product. To do this, you could do so:

select MAX(id) from tb_compra as T1 INNER JOIN tb_compra_produto AS T2 ON T.id = T2.id_compra GROUP BY id_produto

You could use the result of this select MAX as a criterion in your query, your query would look like this:

SELECT a.id_produto, a.valor_unitario, b.data_compra
FROM tb_compra_produto a
INNER JOIN tb_compra b
    ON b.id = a.id_compra
    ORDER BY b.data_compra
INNER JOIN tb_ficha_produto c
    ON c.id_produto = a.id_produto
INNER JOIN tb_ficha d
    ON d.id = :id_ficha //recebo de uma variável
    AND d.id = c.id_ficha
WHERE b.id in (
select MAX(id) from tb_compra as T1 INNER JOIN tb_compra_produto AS T2
ON T.id = T2.id_compra GROUP BY id_produto
)   

Answer 2

use a sub query

select * 
from compra
inner join (select max(id)as id,item from compra group by item) as _last on compra.id=_last.id