>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