| 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: | Whole Thread | Raw Message | 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 |