Search Postgresql Archives

Re: Delete Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux