Latest Row Join

[sysice]

I'm working on a query that grabs a list of all the users plus the latest note for that user. The tables look something like this:

user: id | email
note: id | user | body | posted

So I try something like this, but the body and the posted date don't match

SELECT user.id, user.email, note.body, MAX(note.posted) FROM user JOIN note ON note.user = user.id GROUP BY user.id ORDER BY posted DESC LIMIT 20

Any ideas on how to get this to work would be appreciated.

[sysice]

I found a solution that works, though it isn't pretty.

SELECT user.id, user.email, note.body, note.posted FROM user
JOIN note ON note.user = user.id
WHERE note.id IN (SELECT max(note.id) FROM note WHERE note.user = user.id ORDER BY note.posted DESC)
GROUP BY user.id
order by note.posted desc
LIMIT 20

[swampBoogie]

Some unnecessary stuff there

  SELECT user.id, user.email, note.body, note.posted 
   FROM user JOIN note ON note.user = user.id
 WHERE note.posted IN 
   (SELECT max(note.posted) 
      FROM note 
     WHERE note.user = user.id)
order by note.posted desc
LIMIT 20

[sysice]

Some unnecessary stuff there

Thanks for the tips. They were leftover from other experiments.