select * from a aa
left join
(select * from b temp where exists(select 1 from b where temp.i=b.i and temp.c<=b.c)) bb
on aa.i=bb.i
left join
(select * from b temp where exists(select 1 from b where temp.i=b.i and temp.c<=b.c)) bb
on aa.i=bb.i
(select top 1 C from B where I = A.I) C,
(select top 1 D from B where I = A.I) D
from A
order by I
(select top 1 C from B where I = A.I order by I) C,
(select top 1 D from B where I = A.I order by I) D
from A
From a Join
(Select I,c=Min(c) From b Group by I)c on a.I=c.I Join
B on c.I=B.I and c.C=B.C
select *,
(select top 1 C from B where I = A.I order by C) C,
(select top 1 D from B where I = A.I order by C) D
from A