Return the last purchase for each item in a group

Question

I'm trying to calculate the cost to make one product, for this I have a table with the data sheet to build each product. So, for example, to make the Product A I'll use item a, item d and item g.

Later on I need to calculate this cost based on the last purchase made for each of the 3 items, a, d and g so I know how much it costs to make Product A

*tb_purchase*
id | date | total_value | ...

*tb_purchase_item*
id_purchase | id_item | quantity | value

*tb_sheet*
id | product     | total_cost
1  | "Product A" | 47.00

*tb_sheet_item*
id_sheet | id_item | quantity
1        | 3       | 1.3
1        | 5       | 0.8
1        | 9       | 2.0

To get these values I'm trying This is the query I'm doing to get only the items that is referent to the Product A:

SELECT a.id_item, a.value, b.date

FROM tb_purchase_item a

LEFT JOIN tb_purchase b ON b.id = a.id_purchase
LEFT JOIN tb_sheet_item c ON c.id_item = a.id_item
LEFT JOIN tb_sheet d ON d.id = c.id_sheet

WHERE d.id = :id_sheet

This query is returning only the items I need correctly, but it's returning all the purchases made for those items. I need only tha last one made.

So, instead of this result:

id_item | value | date
3       | 14.89 | 2019-10-12
5       | 07.90 | 2019-10-09
9       | 10.90 | 2019-10-10

I'm getting results like this:

id_item | value | date
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

What should I do to return only the last record for each item? I tried using ORDER BY and then GROUP BY but it return a syntax error.

Answer 1

Try using the funtion max for the fields value and date, also apply `GROUP BY to id_item

For example:

SELECT a.id_item, a.value , max(b.date) as date

FROM tb_purchase_item a

LEFT JOIN tb_purchase b ON b.id = a.id_purchase
LEFT JOIN tb_sheet_item c ON c.id_item = a.id_item
LEFT JOIN tb_sheet d ON d.id = c.id_sheet

WHERE d.id = :id_sheet
GROUP BY a.id_item, a.value
HAVING b.date=MAX(b.date)