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