This question seems easy at first but it isn't. I was reminded of it again on the Postgres mailing list.
(http://article.gmane.org/gmane.comp.db.postgresql.sql/16282)

I have a simple table called notes which contains notes for users.
The table has 4 columns:
note_id (auto-incrementing primary key),
user_id (foreign key to a users table),
note (varchar), and
modified_date (timestamp).

Is there a nice simple query I can run that will return me a list of
all the *latest* notes for all users (users can have many notes in the
table)? I'm trying to figure out a simple way of doing it but seem to
be having some mental block or there is no easy way to do it.

The following query will return me all the latest dates, but I can't
return the note_id or subject with it.

SELECT n.user_id, max(n.modified_date)
FROM notes n
GROUP BY n.user_id
ORDER BY n.user_id
Is this simpler than I am making it?

Answer by: Frank Bax
(http://article.gmane.org/gmane.comp.db.postgresql.sql/16284)

No, it's not "simple". You need to join the results of above sql back to the original table (and relocate the order by clause):

SELECT notes.note_id, notes.user_id, maxx.max_date, notes.note
FROM (SELECT n.user_id, max(n.modified_date) AS max_date FROM notes n GROUP BY n.user_id) AS maxx
JOIN notes ON notes.user_id = maxx.user_id AND notes.modified_date = maxx.max_date
ORDER BY notes.user_id; 

Answer by: PFC
(http://article.gmane.org/gmane.comp.db.postgresql.sql/16285)

If you want the latest by user, you can cheat a bit and use the fact that the id's are incrementing, thus ordering by the id is about the same as ordering by the date field. I know it can be inexact in some corner cases, but it's a good approximation, and very useful in practice :

SELECT user_id, max(note_id) FROM notes GROUP BY user_id;

So :

SELECT * FROM notes WHERE id IN (SELECT max(note_id) FROM notes GROUP BY  user_id) ;

U need antivirus?