Deleting obsolete values

From: Haller Christoph <ch(at)rodos(dot)fzk(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Deleting obsolete values
Date: 2001-10-16 16:45:27
Message-ID: 200110161445.QAA11833@rodos
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stuart Grimshaw 2001-10-16 16:58:32 Re: Why would this slow the query down so much?
Previous Message Szabo Zoltan 2001-10-16 15:57:16 Re: index problem