>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
*---------------------------------------------------------------------------