From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Help with simple query
Date: Wed, 28 Dec 2005 19:41:32 -0500
Message-ID: <5.2.1.1.0.20051228193054.00a3e0a0@pop6.sympatico.ca> (raw)
In-Reply-To: <df01c91b0512281558h3b760e89v80b4354e99183e99@mail.gmail.co m>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: Help with simple query
In-Reply-To: <5.2.1.1.0.20051228193054.00a3e0a0@pop6.sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox