Lowell, you would have to add a where clause to only return those rows that match. Notice below this will return a cartesian product.

select *

from @test1 t

OUTER apply dbo.delimitedsplit8k('d;c;b;a;e',';') myfn

ORDER BY CASE WHEN LEFT(letter,1) = myfn.Item THEN 1 ELSE 2 END,myfn.ItemNumber

The top 5 rows are sorted perfectly but it returned 25 rows instead of 5.

Add the where clause and it works correctly.

select *

from @test1 t

OUTER apply dbo.delimitedsplit8k('d;c;b;a;e',';') myfn

where t.letter = myfn.Item

ORDER BY CASE WHEN LEFT(letter,1) = myfn.Item THEN 1 ELSE 2 END,myfn.ItemNumber