If I have a table of the following:

FROMDATE    TODATE      CATEGORY    LEVEL
01/01/1998  01/01/1999     O          1
01/02/1999  05/23/1999     O          2
05/24/1999  08/14/2001     A          1
08/15/2001  08/01/2002     R          1
08/02/2002  10/10/2003     R          3
10/11/2003  10/31/2003     R          4
11/01/2003  12/01/2003     O          2
12/02/2003  02/15/2004     X          1
02/16/2004                 R          3

I need to find what level this person was the first time they became a category R. The categories and levels have no predefined sequence (i.e. R doesn't always follow O or A and 2 does not always follow 1, etc).

The only way I know to do this now is something like:

select personno, category, level from misshist;
  where personno + dtoc(fromdate,1) in;
  (select personno + dtoc(min(fromdate),1) from misshist where category = 'R' group by personno)

It seems like I can't rely on getting the correct level if I do:

select personno, category, level, min(fromdate);
 from misshist;
 where category = 'R';
 group by personno

(I left personno out of the above table. It would be a unique identifier by individual. The records I showed above would be for 1 individual (i.e. they would all have the same personno)).

Is there any cleaner way to accomplish this?