Skip site navigation (1) Skip section navigation (2)

Re: Identifying obsolete values

From: Masaru Sugawara <rk73(at)echna(dot)ne(dot)jp>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Identifying obsolete values
Date: 2001-10-22 15:19:00
Message-ID: 20011022234357.341F.RK73@echna.ne.jp (view raw or flat)
Thread:
Lists: pgsql-sql
On Wed, 17 Oct 2001 17:17:44 METDST
Haller Christoph wrote:

> I use the second table to identify the actual resp. obsolete ones within the first table. 
> 
> DELETE FROM advncd_tempreftime;
> INSERT INTO advncd_tempreftime 
> SELECT timepoint,mid,lid,sid,MAX(entrancetime) FROM advncd_onfvalue 
>  GROUP BY timepoint,mid,lid,sid ;
> 
> SELECT o.sid,o.timepoint,o.lid,o.mid,o.value FROM advncd_onfvalue o 
> WHERE EXISTS 
> (SELECT * FROM advncd_tempreftime t WHERE 
>     o.timepoint    = t.timepoint    AND
>     o.mid          = t.mid          AND
>     o.lid          = t.lid          AND
>     o.sid          = t.sid          AND
>     o.entrancetime = t.entrancetime 
> ) ; 


Hi,

It seems that a series of the operations can be unified.


SELECT o.sid,o.timepoint,o.lid,o.mid,o.value
   FROM advncd_onfvalue as o
   WHERE EXISTS 
        (SELECT t.timepoint, t.mid, t.lid, t.sid
            FROM advncd_onfvalue as t
            GROUP BY t.timepoint, t.mid, t.lid, t.sid
            HAVING o.timepoint    = t.timepoint    AND
                   o.mid          = t.mid          AND
                   o.lid          = t.lid          AND
                   o.sid          = t.sid          AND
                   o.entrancetime = MAX(t.entrancetime)
        )
;


By the way, a mail server have been downed ?


Regards,
Masaru Sugawara


In response to

pgsql-sql by date

Next:From: Josh BerkusDate: 2001-10-22 17:33:00
Subject: Re: oid's in views.
Previous:From: Esteban Gutierrez AbarzuaDate: 2001-10-22 13:51:00
Subject: pgsql embedded again!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group