使用Select TOP 1子查询获取各组织唯一联系人的问题求助

问题背景 #

我编写了如下SQL查询，期望获取每个组织的首位联系人，但实际结果却是同一联系人重复出现在每一行，而非按组织对应。

原始查询语句 #

select 
    OrgHeader.OH_FullName AS Organisation, OrgAddress.OA_Address1, 
    (select top 1 OrgContact.OC_ContactName 
     from OrgHeader 
     join orgcontact on OH_PK = OC_OH 
     order by OrgContact.OC_PK asc) as Contact,   
    OrgContact.OC_Email AS Email
from 
    OrgHeader 
join
    OrgAddress on OH_PK = OA_OH 
join
    OrgAddressCapability on OA_Pk = PZ_OA and (PZ_IsMainAddress = 1 and PZ_AddressType = 'OFC') 
join
    OrgContact on OH_PK = OC_OH and OC_JobCategory = 'EMF'

当前返回结果 #

补充说明与后续问题 #

感谢各位回复，同时感谢关于邮箱地址的提醒。

我的目标是获取组织列表，每个组织对应一位联系人及其邮箱地址。

按照建议修改后的查询能返回单个联系人，但会返回所有JobCategory为'EMF'的联系人邮箱，导致组织和联系人因该类联系人重复出现。我原以为返回的邮箱会对应该联系人，显然这个假设错误。若给邮箱字段添加TOP 1，能否返回对应联系人的邮箱？

修改后的查询语句 #

select 
    OrgHeader.OH_FullName AS Organisation, OrgAddress.OA_Address1, 
    (select top 1 c2.OC_ContactName 
     from OrgContact c2
     where OH_PK = c2.OC_OH 
     order by c2.OC_PK asc) as Contact,   
    OrgContact.OC_Email AS Email
from 
    OrgHeader 
join
    OrgAddress on OH_PK = OA_OH 
join
    OrgAddressCapability on OA_Pk = PZ_OA and (PZ_IsMainAddress = 1 and PZ_AddressType = 'OFC') 
join
    OrgContact on OH_PK = OC_OH and OC_JobCategory = 'EMF'

内容的提问来源于stack exchange，提问作者Chris Lane