Hi Gregory,

Thanks for the reply and suggestion but one can code a solution in about 10 lines which would be a bit simpler. I was thinking there might be a one- or two-liner but that's cool :)

Later



>>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,
>
>you need two selects
>(1) identify the last datetime per id
>(2) put the result together with a union in order to avoid duplicates
>
>You can avoid the union in the second select by using a full join and a lot of nvl()
>
>

>function Do_it()
>
>	create cursor cursor1 ( ;
>		c1_id	c(2)	default '', ;
>		c1_dt	T	default {//:} ;
>	)
>	
>	create cursor cursor2 ( ;
>		c1_id	c(2)	default '', ;
>		c1_dt	T	default {//:} ;
>	)
>	
>	insert into Cursor1 values ('AA', {^2005/01/01 08:00:00} )
>	insert into Cursor1 values ('AA', {^2005/01/01 09:00:00} )
>	insert into Cursor1 values ('AA', {^2005/01/01 10:00:00} )
>	
>	insert into Cursor1 values ('AB', {^2005/01/01 08:00:00} )
>	insert into Cursor1 values ('AB', {^2005/01/01 09:00:00} )
>	insert into Cursor1 values ('AB', {^2005/01/01 10:00:00} )
>	
>	insert into Cursor2 values ('AB', {^2005/01/01 08:00:00} )
>	insert into Cursor2 values ('AB', {^2005/01/01 09:00:00} )
>	insert into Cursor2 values ('AB', {^2005/01/01 10:00:01} )
>	
>	insert into Cursor2 values ('AC', {^2005/01/01 08:00:00} )
>	insert into Cursor2 values ('AC', {^2005/01/01 09:00:00} )
>	insert into Cursor2 values ('AC', {^2005/01/01 10:00:00} )
>	
>       && (1)
>	select	nvl(T1.c1_id, T2.c1_id)						as id, ;
>		max(max(nvl(T1.c1_dt, { / / :}), nvl(T2.c1_dt, { / / :})))	as dt ;
>		from cursor1 T1 ;
>			full join ;
>				cursor2 T2 on 	(T1.c1_id == T2.c1_id) ;
>			into cursor tmp ;
>		group by 1
>	
>        && (2)
>	select Cursor1.* ;
>		from tmp ;
>			join Cursor1       on	(tmp.id == Cursor1.c1_id) ;
>				  and	(ttoc(tmp.dt,1) == ttoc(Cursor1.c1_dt,1)) ;
>		into cursor Result ;
>	union ;
>	select Cursor2.* ;
>		from tmp ;
>			join Cursor2 on	(tmp.id == Cursor2.c1_id) ;
>				and	(ttoc(tmp.dt,1) == ttoc(Cursor2.c1_dt,1)) ;
>	
>			
>endfunc
>*---------------------------------------------------------------------------
>