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 5BC33D1B297 for ; Wed, 16 Jun 2004 10:10:42 -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 50952-04 for ; Wed, 16 Jun 2004 13:10:43 +0000 (GMT) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id EEFA9D1B215 for ; Wed, 16 Jun 2004 10:10:37 -0300 (ADT) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id i5GCIQeR002831; Wed, 16 Jun 2004 15:18:26 +0300 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id i5GCIQIx002827; Wed, 16 Jun 2004 15:18:26 +0300 Date: Wed, 16 Jun 2004 15:18:26 +0300 (EEST) From: Achilleus Mantzios To: Christoph Haller Cc: pgsql-sql@postgresql.org Subject: Re: How to delete the not DISTINCT ON entries In-Reply-To: <40D03499.84F9354F@rodos.fzk.de> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/140 X-Sequence-Number: 18085 O kyrios Christoph Haller egrapse stis Jun 16, 2004 : > Referring to the DISTINCT ON example > > SELECT DISTINCT ON (location) location, time, report > FROM weatherReports > ORDER BY location, time DESC; maybe smth like delete from weatherReports where (location,time,report) not in (SELECT DISTINCT ON (location) location, time, report FROM weatherReports ORDER BY location, time DESC) Note: Order by is very important, since it affects which rows are deleted. > > How would I delete those entries skipped by the DISTINCT ON expression? > > TIA > > Regards, Christoph > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- -Achilleus