X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id DD5629DCA24 for ; Wed, 28 Dec 2005 20:51:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 80173-04 for ; Wed, 28 Dec 2005 20:51:59 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from tomts43-srv.bellnexxia.net (tomts43-srv.bellnexxia.net [209.226.175.110]) by postgresql.org (Postfix) with ESMTP id 5532C9DCA3A for ; Wed, 28 Dec 2005 20:51:54 -0400 (AST) Received: from acer.sympatico.ca ([65.95.135.127]) by tomts43-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20051229005157.YYAC23065.tomts43-srv.bellnexxia.net@acer.sympatico.ca> for ; Wed, 28 Dec 2005 19:51:57 -0500 Message-Id: <5.2.1.1.0.20051228193054.00a3e0a0@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Wed, 28 Dec 2005 19:41:32 -0500 To: pgsql-sql@postgresql.org From: Frank Bax Subject: Re: Help with simple query In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Originating-IP: [0] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/232 X-Sequence-Number: 23774 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;