am 07.12.2005, um 18:21:25 +1100 mailte Alex folgendes: > Hi, > > I have a table where I store changes made to an order. The looks like > ProdID, ChangeDate, Change1, Change2, ... etc. > Some ProdIDs have multiple records. > > Is there an easy way to delete all records of a ProdID except the most > recent (ChangeDate is timestamp) one? Preferably in one SQL statement? test=# select * from change ; id | datum | text ----+----------------------------+---------- 1 | 2005-12-07 08:28:28.939312 | foo 1 | 2005-12-07 08:28:34.695091 | foo2 1 | 2005-12-07 08:28:37.150354 | foo3 1 | 2005-12-07 08:28:43.263171 | foo_last 2 | 2005-12-07 08:28:48.419252 | foo 2 | 2005-12-07 08:28:55.819969 | foo_last (6 rows) test=# begin; BEGIN test=# delete from change where id || ':' || datum not in (select id || ':' || max(datum) from change group by id order by 1); DELETE 4 test=# select * from change ; id | datum | text ----+----------------------------+---------- 1 | 2005-12-07 08:28:43.263171 | foo_last 2 | 2005-12-07 08:28:55.819969 | foo_last (2 rows) But i'm not sure if this works correctly for you. HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
