sql query sub select on itself confusion

Question

I'm trying to write a sql statement which returns bad rows from my table. I have a table with rows:

Key | Date | Indicator
1 | 10/10/08 | SE
1 | 10/11/09 | CB
1 | 10/12/09 | CE
1 | 10/13/09 | TR
2 | 1/1/09 | SE
3 | 10/10/08 | SE
3 | 10/13/09 | CE
3 | 10/15/09 | SL

So what I want returned would be all rows where a key has an indicator of CE then I want the row that is exactly after it date wise.
EX. my result would then be:

1 | 10/13/09 | TR
3 | 10/15/09 | SL

My problem is I can't figure out how to join the table on itself with the conditions listed above. Please help with any suggestions.

Answer 1

This should give you all of the rows even if a CE occurs more than once for a key.

select t1.*
from myTable t1
inner join 
    (select t2.[key], min(t2.date) minDate   
    from myTable t2   
    inner join (   
      select CE.[key], CE.date     
      from myTable CE   
      where CE.indicator = 'CE'  
      ) as CEs 
    on CEs.[key] = t2.[key] 
    and CEs.date < t2.date 
    group by t2.[key]) t2s
on t2s.[key] = t1.[key]
and t1.date = t2s.minDate
order by 1, 2

Answer 2

Probably not very fast, but my first guess.

select * from table where 'CE' = 
(select top(1) indicator from table 
    t1 where t1.date < table.date and t1.key = table.key order by date desc)