Re: Help with simple query

At 06:58 PM 12/28/05, Collin Peters wrote:
>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?

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;