Received: from mail_exchange.westcountrypublications.co.uk ([195.171.163.135]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9IDUXP44304 for ; Thu, 18 Oct 2001 09:30:33 -0400 (EDT) (envelope-from SHenshall@westcountrypublications.co.uk) Received: by MAIL_EXCHANGE with Internet Mail Service (5.5.2650.21) id <475AG883>; Thu, 18 Oct 2001 14:28:34 +0100 Message-ID: From: "Henshall, Stuart - WCP" To: "'Haller Christoph'" , pgsql-sql@postgresql.org Subject: Re: Deleting obsolete values Date: Thu, 18 Oct 2001 14:28:31 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain X-Archive-Number: 200110/250 X-Sequence-Number: 5205 DELETE FROM partitur WHERE EXISTS (SELECT * FROM partitur AS ss_partitur WHERE ss_partitur.userid=partitur.userid AND ss_partitur.ts>partitur.ts); Seems like it should seems like it should delete all old values (however I have not tested it) - Stuart > -----Original Message----- > From: Haller Christoph [SMTP:ch@rodos.fzk.de] > Sent: Tuesday, October 16, 2001 5:45 PM > To: pgsql-sql@postgresql.org > Subject: Deleting obsolete values > > 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