Delete from partitur where userid NOT IN (SELECT DISTINCT ON (userid) userid, val, ts FROM partitur)
"Haller Christoph" <[EMAIL PROTECTED]> wrote in message 200110161445.QAA11833@rodos">news:200110161445.QAA11833@rodos... > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
