Help with simple query

From: Collin Peters <cadiolis(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Help with simple query
Date: 2005-12-28 23:58:12
Message-ID: df01c91b0512281558h3b760e89v80b4354e99183e99@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Frank Bax 2005-12-29 00:41:32 Re: Help with simple query
Previous Message Alvaro Herrera 2005-12-28 21:24:02 Re: DISTINCT with NULLs and INT fields