Thanks Fabio. Rich Pupko gives a nice one-line solution. Thanks again!



>>Hi All,
>>
>>I have two tables which are identical in all respects; number of records, fields per record, and even the value of all fields except for the value of a particular datatime field. The records contain user access info, one record per user, and the datetime field contains the last access datetime.
>>
>>I need to extract from these two files the latest record only for each user. So either the record from file1 or from file2, whichever has the latest datetime access.
>>
>>I can obviously code this easily enough but was wondering if there was an easy one-liner SELECT statement or some such?
>>
>>Thanks,
>
>Hi Jos,
>
>If I have understood correctly
>
>

>create cursor cursor1 ( id_User	I	, dt_Last	T, F1 C )
>
>create cursor cursor2 ( id_User	I	, dt_Last	T, F1 C )
>
>* 1 to 1 relation
>&& 1 record x User RECCOUNT1=RECCOUNT2
>insert into Cursor1 values (1, {^2005/01/01 08:00:00},'A' )
>insert into Cursor2 values (1, {^2005/01/01 10:00:00},'B' )
>
>insert into Cursor1 values (2, {^2005/01/01 08:00:00},'D' )
>insert into Cursor2 values (2, {^2005/01/01 07:00:00},'C' )
>
>insert into Cursor1 values (3, {^2005/01/01 08:00:00},'Z' )
>insert into Cursor2 values (3, {^2005/01/01 09:00:00},'W' )
>
>
>* that choice for T1.dt_Last=T2.dt_Last ?
>
>select	T1.id_User ;
>,	IIF(T1.dt_Last>T2.dt_Last,	T1.dt_Last	,T2.dt_Last	) dt_Last;
>,	IIF(T1.dt_Last>T2.dt_Last,	T1.F1		,T2.F1		) F1;
>,	IIF(T1.dt_Last>T2.dt_Last,	1			,2			) Source;
>into cursor tmp ;
>	from		cursor1 T1 ;
>		join	cursor2 T2 ;
>			on 	T1.id_User = T2.id_User
>
>* OR
>select	T1.*,	1 Source;
>into cursor tmpUnion ;
>	from		cursor1 T1 ;
>		join	cursor2 T2 ;
>			on 	T1.id_User = T2.id_User AND T1.dt_Last>T2.dt_Last;
>UNION ALL;
>select	T2.*,	2 ;
>	from		cursor1 T1 ;
>		join	cursor2 T2 ;
>			on 	T1.id_User = T2.id_User AND T1.dt_Last<=T2.dt_Last
>

>
>Fabio