Re: 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 ===