I am producing a database in MySQL/PHP. I have two tables - matters and advice. The fields are:
matters = mattered, matterdate, refno
advice= adviceid, matterid, advicedate
For each matter there can be a number of advice records.
I want to extract a list that shows all of the matters but only the latest advice entry for each one (i.e. the one with the highest adviceid) from the advice table in the form:
mattered | ref no | adviceid | advicedate
in order of advice date.
I have tried various queries but nothing seems to give the results I want.
Does anyone have any suggestions?
if you are willing to give it a shot yourself, google the mysql site for "groupwise max"
use the solution which joins to a subquery
Thank you - I think I've done it now. It's knowing where to look so thanks for the link.