Thread: Help with simple query

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?

Regards,
Collin

Collin Peters <cadiolis@gmail.com> writes:
> 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)?

You can use SELECT DISTINCT ON for that, if you don't mind using a
Postgres-only feature.  See the "weather reports" example in the SELECT
reference page.

If you want to stick to portable SQL, you can still do it, but it's
pretty ugly and slow.  Look in the list archives for previous
discussions.
        regards, tom lane

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; 

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) ;

Can postgres use the index on these max() clauses now ?

or, from the "stupid tricks" category:
SELECT  n.user_id,   max(lpad(extract(epoch from n.modified_date),12,'0') || ' ' || note)FROM notes nGROUP by n.user_id

i am not *really* suggesting this!