Re: Identifying obsolete values

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