I provided one solution in the other thread. Another way to get the same info can be found in sample by Cetin Basoz Re: Records matching MAX(Date) Thread #690278 Message #690287

select * from myGrouper A where ddate = ;
(select max(ddate) from myGrouper B where a.ino = b.ino) ;
order by ino

e.g. in your case

SELECT ag.nstock, ag.iid FROM Agenda ag ;
WHERE ag.ddate = (select max(dDate) from Agenda ag2 ;
where ag.iTraiterID = ag2.iTraiterID and ag2.dDate < vp_date)

though this again have a problem if you have two (or more) records with the same date. This query will return all records.

>>First join is incorrect if you have multiple previous records.
>
>just ...
>
> *-- To test a condition
> INSERT INTO Agenda (iid , ddate , itraiterId , nstock ) VALUES ( 4 , vp_date -2 , 3 , 6)
> INSERT INTO Agenda (iid , ddate , itraiterId , nstock ) VALUES ( 4 , vp_date -3 , 3 , 4)
> INSERT INTO Agenda (iid , ddate , itraiterId , nstock ) VALUES ( 4 , vp_date -4 , 3 , 2)
>
>
>I want the Only the value for vp_date -2 ( = the most recent day of vp_date )
>
>>
>>>Naomi,
>>>
>>>
>>>You are right.
>>>
>>>

>>> UPDATE ap ;
>>>    SET nstock = pd.nstock ;
>>>    FROM Agenda ap;
>>>    JOIN (;
>>>    SELECT ag.nstock, ag.iid FROM Agenda ag WHERE ag.ddate < (vp_date) ;
>>>    UNION ALL ;
>>>    SELECT T.nstock, dt1.iid ;
>>>    FROM ( ;
>>>    SELECT iid,itraiterId ;
>>>    FROM Agenda ag2 ;
>>>    WHERE ddate = (vp_date) ;
>>>    AND NOT EXISTS (SELECT * FROM Agenda ag3 ;
>>>    WHERE ag3.ddate < (vp_date) ;
>>>    AND ag3.iid  = ag2.iid) ;
>>>    ) dt1 ;
>>>    JOIN Traiter T ON T.iid = dt1.itraiterId 	;
>>>    ) pd ON ap.iid = pd.iid ;
>>>    WHERE ap.ddate = vp_date
>>>

>>>
>>>Give a good result
>>>
>>>bernhart
>>>