X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D925A5396C for ; Tue, 19 Apr 2005 05:50:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02498-06 for ; Tue, 19 Apr 2005 08:50:39 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by svr1.postgresql.org (Postfix) with ESMTP id 2F8365388C for ; Tue, 19 Apr 2005 05:50:38 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so2004781wra for ; Tue, 19 Apr 2005 01:50:40 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=bi4WaYplAhfTILJFkVePwDkkmKg/QvltRVpimMzx27Ujj7n16i3Cq4JK6AA2yRwdKYCzo1M+4GrO9fOmUvxrR3VJTgqG295mVbUDdJgUXSo3TzSAmheqnRZ/cDAS4YxkQJTJ4MmLSipS4Foz4lXSNVrKr/yHBgGx2pIo7jyoStU= Received: by 10.54.49.6 with SMTP id w6mr3145371wrw; Tue, 19 Apr 2005 01:50:40 -0700 (PDT) Received: by 10.54.105.19 with HTTP; Tue, 19 Apr 2005 01:50:40 -0700 (PDT) Message-ID: <9febecb505041901502f116211@mail.gmail.com> Date: Tue, 19 Apr 2005 09:50:40 +0100 From: etur Reply-To: etur To: pgsql-sql@postgresql.org Subject: select the last post Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.398 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/347 X-Sequence-Number: 21240 hi there, hi have a table called forum, and i've got this query: this query select the messages and the number of replies to the message individualy. i'm trying to select the last post of each set of replies, like we see in the foruns. does anyone can help me best regards, etur SELECT id, subject, to_char(post_time, 'YYYY.MM.DD @ HH24:MI:SS'::text) AS post_time, content, login, messages.parent_id, replies.answered FROM ( SELECT f.id, f.subject, f.post_time, f.content, u.login, f.parent= _id FROM forum f, system_users u WHERE f.parent_id IS NULL AND f.user_id =3D u.id) messages LEFT JOIN ( SELECT forum.parent_id, count(forum.id) AS answered FROM forum GROUP BY forum.parent_id) replies=20 =09ON messages.id =3D replies.parent_id;