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;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
