SELECT personno, category, level ; FROM misshist ms1 ; WHERE fromdate IN ; (SELECT MIN(fromdate) FROM misshist ms2 ; WHERE category = 'R' AND ms2.personno = ms1.personno)>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).
>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) >>
>select personno, category, level, min(fromdate); > from misshist; > where category = 'R'; > group by personno >>