Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9JD5VP32961 for ; Fri, 19 Oct 2001 09:05:31 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f9JCxXx49952 for pgsql-sql@postgresql.org; Fri, 19 Oct 2001 08:59:33 -0400 (EDT) (envelope-from news) From: "Pat M" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Deleting obsolete values Date: Fri, 19 Oct 2001 05:56:02 -0700 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 39 Message-ID: <9qp83i$1gos$1@news.tht.net> References: <200110161445.QAA11833@rodos> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700 To: pgsql-sql@postgresql.org X-Archive-Number: 200110/317 X-Sequence-Number: 5270 Delete from partitur where userid NOT IN (SELECT DISTINCT ON (userid) userid, val, ts FROM partitur) "Haller Christoph" wrote in message news:200110161445.QAA11833@rodos... > This may look familiar to you - it was on the list last month. > Consider the following table > create table partitur > (userid text, val integer, ts timestamp DEFAULT NOW() ); > Do some inserts > insert into partitur values('Bart', 1440); > insert into partitur values('Lisa', 1024); > insert into partitur values('Bart', 7616); > insert into partitur values('Lisa', 3760); > insert into partitur values('Bart', 3760); > insert into partitur values('Lisa', 7616); > To retrieve the latest values (meaning the last ones inserted) > Tom Lane wrote > >This is what SELECT DISTINCT ON was invented for. I don't know any > >comparably easy way to do it in standard SQL, but with DISTINCT ON > >it's not hard: > >SELECT DISTINCT ON (userid) userid, val, ts FROM partitur > >ORDER BY userid, ts DESC; > > My question now is > Is there a way to delete all rows the select statement did not > bring up? > After that *unknown* delete statement > select userid, val, ts from partitur ; > should show exactly the same as the SELECT DISTINCT ON (userid) ... > did before. > > Regards, Christoph > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)