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@schollglas.com>
To: <pgsql-general@postgresql.org>
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
>
>
