Converting outer apply with Correlated subquery in SQL Server to SQLite
SQLite does not support lateral or correlated joins.  You can work around that using a join condition.  For example:

create table t1 (id int, name text);
create table t2 (id int, t1id int references t1(id), name text);
insert into t1 values (1, 'a'), (2, 'b'), (3, 'c');
insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1');


Now we'd like to look up at most one row from t2, sorted by name.  Here's one approach that will work in SQLite:

select  *
from    t1
left join
        t2
on      t2.id =
        (
        select  id
        from    t2
        where   t1.id = t2.t1id
        order by
                name desc
        limit   1
        )


Example at SQL Fiddle.