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
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 |