SELECT table1.pk1, (select top 1 somecharfield from table2 where pk1=table1.pk1 order by pk2) as somecharfield from table1