mysql - SQL - Group By where rows have the most recent date -

mysql - SQL - Group By where rows have the most recent date -

i having next tables , wondering how query on it.

table recipe

id | name 0 | apple pie 1 | pizza

table ingredient

recipeid | timestamp | name | cost 0 | 10-2-2014 | apple | 1.20 0 | 7-2-2014 | apple | 1.14 0 | 9-2-2014 | flour | 2.00 1 | 9-2-2014 | tomato | 1.12

how can grouping recipeid on next results

recipeid | name | total_latest_ingredient_price 0 | apple pie | 3.20 1 | pizza | 1.12

the thought query should take ingredients latest dates. cost apple 10-2-2014 taken , not 1 (or both) 7-2-2014. latest dates.

this might tough cookie ( or not! ) hope can help me out!

assuming combination of (recipeid, timestamp, name) unique on ingredient table, , assuming column named timestamp stored in canonical form (e.g. either mysql date, datetime, timestamp datatype or character datatype format such comparing of values yield "latest" timestamp value...

the normative pattern utilize inline view retrieve "latest" timestamp, , utilize bring together operation retrieve entire row.

select g.recipeid , sum(g.price) `total_latest_ingredient_price` ingredient g bring together (select h.recipeid , h.name , max(h.timestamp) `timestamp` ingredient h grouping h.recipeid , h.name ) on i.recipeid = g.recipeid , i.name = g.name , i.timestamp = g.timestamp grouping g.recipeid

the inline view aliased i gets "latest" timestamp (assuming, again, timestamp column canonical form, such "maximum" value guaranteed "latest" value. true if datatype of column named timestamp mysql date, datetime, or timestamp.)

the outer query references rows returned i, , performs bring together operation retrieve entire row table (aliased g) related price.

to name column recipe table, we'd add together bring together operation table...

select g.recipeid , r.name , sum(g.price) `total_latest_ingredient_price` ingredient g bring together (select h.recipeid , h.name , max(h.timestamp) `timestamp` ingredient h grouping h.recipeid , h.name ) on i.recipeid = g.recipeid , i.name = g.name , i.timestamp = g.timestamp bring together recipe r on r.id = g.recipeid grouping g.recipeid

mysql sql database