declare @ownerId int = ; declare @ownerType varchar(3) = ; with memberGroupsRaw as ( select opr.operatorId, opr.companyName, opr.oneFSId, row_number() over ( partition by opr.oneFSId order by operatorId ) as mappingPriority from tbl_DW_CRMOperators#_tableSuffix# opr with (nolock) where opr.ownerId = @ownerId and opr.ownerType = @ownerType and opr.oprCompanyType = 'C' and opr.oneFSId > 0 select opr.operatorId, opr.companyName, opr.fsl_choId oneFSId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.oprCompanyType = 'C' and opr.fsl_choId > 0 ), memberGroups as ( select operatorId, companyName, oneFSId from memberGroupsRaw with (nolock) where mappingPriority = 1 ) #forJSON( 'memberGroups', attributes.dataStore, 'companyName' )#