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 по дате отправления: