X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 21F9A52800 for ; Wed, 1 Jun 2005 12:20:26 -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 05498-04 for ; Wed, 1 Jun 2005 15:20:19 +0000 (GMT) Received: from amanda.contactbda.com (ipn36372-e65122.cidr.lightship.net [216.204.66.226]) by svr1.postgresql.org (Postfix) with ESMTP id 7D6BF52876 for ; Wed, 1 Jun 2005 12:20:18 -0300 (ADT) Received: from amanda.contactbda.com (amanda.contactbda.com [192.168.1.2]) by amanda.contactbda.com (8.12.11/8.12.11/Debian-3) with ESMTP id j51FK814018697; Wed, 1 Jun 2005 11:20:08 -0400 From: "Jim Buttafuoco" To: FC , pgsql-general@postgresql.org Reply-To: jim@contactbda.com Subject: Re: SQL question. Date: Wed, 1 Jun 2005 11:20:08 -0400 Message-Id: <20050601151852.M49412@contactbda.com> In-Reply-To: <6EE9301F-4C50-4160-9DC0-667826764A74@myamail.com> References: <6EE9301F-4C50-4160-9DC0-667826764A74@myamail.com> X-Mailer: Open WebMail 2.41 20040926 X-OriginatingIP: 192.168.1.1 (jim) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.155 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200506/19 X-Sequence-Number: 78727 how about (untested) select * from ( SELECT DISTINCT ON (linkid) * FROM all_links WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',TIMESTAMP '2005-06-01') ORDER BY linkid ) A ORDER BY read_date DESC limit 100 ---------- Original Message ----------- From: FC To: pgsql-general@postgresql.org Sent: Wed, 1 Jun 2005 16:40:48 +0200 Subject: [GENERAL] SQL question. > Hello SQL Aces ! > > I want to do a select on a table distinct on linkid and sorted by > date. I have try this > > SELECT DISTINCT ON (linkid) * FROM all_links > WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', > TIMESTAMP '2005-06-01') ORDER BY linkid, read_date; > > With this request "works" but the raws are sorted as I want. To have > the rows sorted by date I have done this using a temporary table and > that works exactly as I want. > > CREATE LOCAL TEMP TABLE temp_links WITHOUT OIDS AS > SELECT DISTINCT ON (linkid) * FROM all_links > WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', > TIMESTAMP '2005-06-01') ORDER BY linkid; > > SELECT * from temp_links ORDER BY read_date DESC limit 100 > > My question is... How can I do the same thing in the more efficient > way and without using a temporary table. Since I am using PHP and the > table is not deleted at the end of the program because PHP keeps the > connection to the database open. > > Thanks for any suggestions. > Fred > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) ------- End of Original Message -------