Received: from resy5.fzk.de (resy5.fzk.de [141.52.68.8]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9GEkcP61463 for ; Tue, 16 Oct 2001 10:46:39 -0400 (EDT) (envelope-from ch@rodos.fzk.de) Received: by rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id QAA02913 for ; Tue, 16 Oct 2001 16:48:14 +0200 (METDST) From: Haller Christoph Message-Id: <200110161445.QAA11833@rodos> Subject: Deleting obsolete values To: pgsql-sql@postgresql.org Date: Tue, 16 Oct 2001 16:45:27 METDST X-Mailer: Elm [revision: 212.4] X-Archive-Number: 200110/209 X-Sequence-Number: 5164 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