Deleting obsolete values
| От | Haller Christoph |
|---|---|
| Тема | Deleting obsolete values |
| Дата | |
| Msg-id | 200110161445.QAA11833@rodos обсуждение исходный текст |
| Список | 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
В списке pgsql-sql по дате отправления: