>hI boris
>
>>You get what you ask for :-)
>>There is NO guarantee that MAX(E.Ltransfert) will be from the last record. This will give just biggest value of this field.
>>How do you define the records ass LAST?
>
>
>i want the record of etats.dbf where etats.iid = 4818 ( the bigst primary key )
>
>what can i put in my sql querie to have etats.ltransfert ?
>why the sql give informations from 2 records !
>
>if i do'nt put max( etats.ltransfert ) i have a error ( group by is not valid .... )
>
>bernhart
>
>
>>>hi all,
>>>
>>>i want a cursor with customer informations ( here the custmer name is 'boyne' )
>>>
>>>i want the LAST record of children table ( Etats.dbf )
>>>
>>>The key between the 2 tables is a integer ( Iresidentid for etats.dbf and IID for resident.dbf )
>>>
>>>
>>>here i have a strange result ! Please see the attachment ...
>>>
>>>Cor9.IetatId give a good result ( it 's the last record of etats.dbf )
>>> Cor9.nType is bad result ! ( it's the first record of etats.dbf)
>>> Cor9.ccat_Physiq , give a good result ( it 's the last record of etat.dbf )
>>> cor9.ddebut, give a good result ( it 's the last record of etat.dbf )
>>> cor9.Ltransfert; is bad result ! ( it's the first record of etats.dbf)
>>>
>>>
>>>SET ENGINEBEHAVIOR 90 is Ok in beginning...
>>>
>>>What happend ?
>>>
>>>bernhart
>>>
>>>
>>>

>>>
>>>SET ENGINEBEHAVIOR 90
>>>
>>> m.cfiledata = "C:\cdbk90\amline\data2\"
>>> USE (  m.cfiledata + "AMLINE!Resident") IN 0
>>> USE (  m.cfiledata + "AMLINE!Etats") IN 0
>>> 
>>>
>>> SELECT R1.Cnomcustomer ,;
>>>  R1.Iid ,;
>>>  Cor9.IetatId  , ;
>>>  Cor9.nType  ,;
>>>  Cor9.ccat_Physiq , ;
>>>  cor9.ddebut,;
>>>  cor9.Ltransfert;
>>>   FROM Amline!Resident R1, ;
>>>    ( SELECT  E.iresidentId , ;
>>>  MAX( NVL( E.nType, 0 )) AS nType ,;
>>>  MAX( E.iid ) AS IetatId ,;
>>>  MAX(E.ccat_Physiq) AS ccat_Physiq,;
>>>  CAST( MAX( NVL( E.lTransfert , .F. ) ) AS L ) AS lTransfert  ,;
>>>  MAX( E.ddebut ) AS ddebut;
>>>  FROM Etats E GROUP BY E.iresidentId   ) AS Cor9 ;
>>>  WHERE R1.iid = cor9.iresidentId ;
>>>  AND  EMPTY( TTOD( R1.dsortie ) )  ORDER BY R1.Cnomcustomer INTO CURSOR temp
>>>
>>>
>>> SELECT temp
>>>
>>>
>>> BROWSE NOWAIT
>>>
>>> SELECT etats
>>> SET FILTER TO iresidentid = temp.iid
>>> 
>>> BROWSE NOWAIT
>>>
>>>

>>
>>


Try this:

SELECT R1.Cnomcustomer ,;
       R1.Iid ,;
       Cor9.IetatId  , ;
       Cor9.nType  ,;
       Cor9.ccat_Physiq , ;
       cor9.ddebut,;
       cor9.Ltransfert;
FROM Amline!Resident R1;
INNER JOIN (SELECT  Etats.iresidentId , ;
                    NVL( Etats.nType, 0 )         AS nType ,;
                    Etats.iid                     AS IetatId ,;
                    Etats.ccat_Physiq             AS ccat_Physiq,;
                    NVL( Etats.lTransfert , .F. ) AS lTransfert  ,;
                    Etats.ddebut                  AS ddebut;
            FROM Etats;
            INNER JOIN (SELECT IresidentId,;
                               MAX(IID) AS IId;
                        FROM Etats;
                        GROUP BY IresidentId) Etst ON Etats.IresidentId = Etst.IresidentId AND;
                                                      Etats.IId         = Etst.IID) Cor9 ;
       ON R1.iid = cor9.iresidentId ;
WHERE EMPTY( TTOD( R1.dsortie ) );
ORDER BY R1.Cnomcustomer;
INTO CURSOR temp

NOT TESTED!