Thread: Query problems

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    7
    Rep Power
    0

    Query problems


    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?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    7
    Rep Power
    0
    Thank you - I think I've done it now. It's knowing where to look so thanks for the link.

IMN logo majestic logo threadwatch logo seochat tools logo