Retrieving the latest note (by timestamp) in a single query from a 1:n table

Question

Let's say I have two tables, users and notes. Let's say the schemas look like this:

users
id, name, field2, field3

notes
id, user_id, subject, heading, body, timestamp

What I want to do is select every user, and the LATEST (just 1) note posted by each user based on the timestamp to show in an overview report.

How would I go about doing this? Please note that the DB is MySQL.

Thanks!

Answer 1

select u.id, u.name, n.id as note_id, n.subject, n.heading, n.body, n.timestamp
from users u
left outer join (
    select user_id, max(timestamp) as timestamp
    from notes
    group by user_id
) nm
left outer join notes n on nm.user_id = n.user_id and nm.timestamp = n.timestamp

Note that this could potentially return duplicates if the user has two notes with the exact same timestamp. I have assumed this is not the case.

Answer 2

 select users.name, notes.subject, notes.heading, notes.body
 from users, notes
 where users.id = notes.user_id
 and notes.timestamp = (select max(timestamp) from notes where user_id = users.id)

Answer 3

SELECT *
FROM `users`
LEFT JOIN `notes`
ON `user_id` = `users`.`id`
WHERE `notes`.`timestamp` = (
    SELECT MAX(`timestamp`)
    FROM `notes` AS `notes_1`
    WHERE `notes_1`.`user_id` = `notes`.`user_id`
)