Try

select C1.*, C2.LASTNAME 
	from COMPANY C1 
	LEFT JOIN ( select top 1 LastName, comp_pk from 
				CONTACT c3 WHERE C1.comp_pk = C3.comp_pk order by OrderNo ) C2 on C1.comp_pk = C2.comp_pk 

-- or

select C1.*, 
	( select top 1 LastName from CONTACT c3 WHERE C1.comp_pk = C3.comp_pk order by OrderNo ) AS LASTNAME 
	from COMPANY C1 

>
>I sense this there is something real simply I am missing in the following SQL Select but I don't seem to be understand it.
>
>I am trying to select one record from a Company table and one record from a Contact table (related to Company by column COMP_PK). But I get a NULL in column LASTNAME. Here is my SQL Select:
>
>

>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
>

>
>What is wrong with my SQL Select?