Re: Delete Question

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?

Thanks for any advise

Alex

On 12/7/05, Alex <alex(at)meerkatsoft(dot)com> wrote:
>
> 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?
>

delete from table_name where exists (select * from table_name x where
x.prodid = table_name.prodid and x.changedate > table_name.changedate);

this should work.

depesz

On Dec 7, 2005, at 16:21 , Alex wrote:

> 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?

Here's one way to do it, though not it one SQL statement:

create table copy_of_original_table as
select distinct on ("ProdID") "ProdID", ...
from original_table
order by "ChangeDate" desc;

truncate original_table;

insert into original_table ("ProdID", ... )
select "ProdID", ...
from copy_of_original_table;

Note you need to quote the column names if case is important.

Michael Glaesemann
grzm myrealbox com

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

Thanks ,
this one works beautifully.

Alex

PS: also thanks to the other suggestions, have a look at this one

hubert depesz lubaczewski wrote:

> On 12/7/05, *Alex* <alex(at)meerkatsoft(dot)com
> <mailto:alex(at)meerkatsoft(dot)com>> wrote:
>
> 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?
>
>
> delete from table_name where exists (select * from table_name x where
> x.prodid = table_name.prodid and x.changedate > table_name.changedate);
>
> this should work.
>
> depesz

Hi All!

Tell me please - is there any script for conversion MySQL dump
file into PostgreSQL ?

Thanks!

Igor.

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(dot)kretschmer(at)schollglas(dot)com>
To: <pgsql-general(at)postgresql(dot)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
>
>

DELECT FROM table
WHERE (ProdID,ChangeDate) not in
(SELECT ProdID,MAX(ChangeDate) FROM table
GROUP BY ProdID)

I hope this works.

On 12/7/05, Alex <alex(at)meerkatsoft(dot)com> wrote:
> 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?
>
> Thanks for any advise
>
> Alex
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Regards
Pandu

am 07.12.2005, um 8:54:56 +0100 mailte A.j. Langereis folgendes:
> 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);

Oh, thank you!

I have tried this way, but with a wrong syntax.

Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

am 07.12.2005, um 10:53:27 +0300 mailte go KEY802207 folgendes:
> Hi All!
>
> Tell me please - is there any script for conversion MySQL dump
> file into PostgreSQL ?

http://techdocs.postgresql.org/#convertfrom

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===