>>>I have a table with about 16000 records in it. These are monthly claims. The table contains original, and revised claims. The original claims have claims.crecno = 0, and each revision to that claim increments the crecno by 1. So for any month, there is an original and from 0 to many revisions. I want a SELECT that I can retrieve the latest revised claims for a given month into a cursor. So that if the client just sent in the original(crecno=0) I get the original claim, but if there are any revisions, I get the claim with the highest(crecno).
>>>Table: claims
>>>co n2
>>>dist c4
>>>month n2
>>>year n4
>>>crecno n2
>>>data...
>>>
>>>str(co,2)+dist is the customer number
>>>
>>>I want something like:
>>>SELECT * FROM claims WHERE month = 12 AND year = 1997 AND Max(crecno);
>>>INTO CURSOR claimlist
>>>
>>>Any suggestions?
>>>
>>>Shane
>>Shane,
>>I think you should do it in two SQL.
>>

>>select str(co,2)+dist, ;
>>       str(co,2)+dist+padl(crecno,2,"0") as identifier ,;
>>       max(crecno) ;
>>  from claims ;
>>  WHERE month = 12 AND year = 1997 ;
>>  group by 1 ;
>>  where .t. ;
>>  into cursor pass1
>>SELECT * FROM claims ;
>>  WHERE str(co,2)+dist+padl(crecno,2,"0") in ;
>>       (select identifier from pass1) ;
>>       INTO CURSOR claimlist
>>

>>Cetin
>
>Thanks for the answer, I'll give that a try. I tried the following and seemed to get the correct results, but I wonder if I am missing something that will bite me later:
>
>SELECT *,MAX(crecno) AS last;
>FROM claims;
>WHERE month = 12 AND year = 1997;
>GROUP BY co,dist;
>ORDER BY co,dist;
>INTO CURSOR claimlist
>
>Thanks again for the help
>
>Shane
Shane this would bite you if revisions are not entered by increasing order. In your case it seem to not harm though (Your entry is already ordered on revision). If by any reason there were data entered in order like :
RevNum Custno Data
1 1 a
2 1 b
4 1 d
3 1 c
This would catch you with a return row like :
4 1 c && RevNum is max but data comes from lastrow.
Cetin