• It's not too hairy. I don't know how it would perform against a large data set. This was an interesting exercise.

    SELECT * into #TMP FROM (

    select '01' as a1, 'a' as a2 union all

    select '02' as a1, '' as a2 union all

    select '03' as a1, '' as a2 union all

    select '04' as a1, 'a' as a2 union all

    select '05' as a1, '' as a2 union all

    select '06' as a1, 'a' as a2 union all

    select '07' as a1, '' as a2 union all

    select '08' as a1, '' as a2 union all

    select '09' as a1, 'a' as a2 union all

    select '10' as a1, 'a' as a2 union all

    select '11' as a1, 'a' as a2 union all

    select '12' as a1, '' as a2 union all

    select '13' as a1, '' as a2 union all

    select '14' as a1, '' as a2 union all

    select '15' as a1, '' as a2 union all

    select '16' as a1, '' as a2 union all

    select '17' as a1, 'a' as a2 union all

    select '18' as a1, 'a' as a2) D

    SELECT T.A1, T.A2,

    CASE WHEN T.A2 = '' THEN NULL

    ELSE

    ISNULL((select MIN(X.A1) from #TMP X WHERE X.A2 = '' AND X.A1 < T.A1 AND X.A1 >

    (SELECT TOP 1 D.A1 FROM #TMP D WHERE D.A2 = 'a' AND D.A1 < T.A1 ORDER BY D.A1 DESC)), T.A1) END AS A3

    FROM #TMP T ORDER BY T.A1 ASC