Postgresql supports records in the where clause i.e. you can compare multiple columns simultaneously: > test=# delete from change where id || ':' || datum not in (select id || ':' || max(datum) from change group by id order by 1); could therefore be rewritten to: delete from change where (id, datum) in (select id, max(datum) from change group by id); Yours, Aarjan Langereis ----- Original Message ----- From: "A. Kretschmer" <andreas.kretschmer@xxxxxxxxxxxxxx> To: <pgsql-general@xxxxxxxxxxxxxx> Sent: Wednesday, December 07, 2005 8:47 AM Subject: Re: [GENERAL] Delete Question > 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 === > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
