>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