Hi Tom,
Your second select is based on misconseption that values of pk, pid, lineid, origrev and rectype fields are comming from the same record as MAX(RevNum). In fact they're comming from the last physical record in the group. To verify that you can change the order of the records in the file to the opposite an run you select again. ALso queries like that, even if they produce correct result, cannot be ported to any other backend.
>I made your file comma delimited but all the rest is the same. Try this solution:
>
>
>CREATE CURSOR xx(pk i, pid C(4), lineid C(3), revnum i, origrev i, rectype c(1))
>APPEND FROM xx.txt DELIMITED
>
>SELECT pk, pid, lineid, revnum, origrev, rectype ;
> FROM xx WHERE rectype = "A" ;
>UNION ;
>SELECT pk, pid, lineid, MAX(revnum) revnum, origrev, rectype ;
> FROM xx WHERE rectype = "I" AND lineid IN ;
> (SELECT lineid FROM xx WHERE rectype ="A") ;
> GROUP BY lineid INTO CURSOR cfini
>
>
< snip >
--sb--