>select C1.*, C2.LASTNAME from COMPANY C1 LEFT JOIN ( select top 1 LastName, comp_pk from >CONTACT order by OrderNo ) C2 on C1.comp_pk = C2.comp_pk where 1=1 >>
select C1.*, C2.LastName from Company C1 Left JOIN Contact C2 on C1.comp_pk = C2.comp_pk where C2.comp_pk IS NULL or C2.CompPK = (select top 1 Comp_pk from Contact where Contact.Comp_pk = C1.Comp_pk order by OrderNo )See also http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/including-an-aggregated-column-s-related