With DEDUPE AS (
select title.title_id,edition.title,party.party_id,party_type_code,(party.title + ' ' + party.first_name+' '+party.surname) as name,
ISNULL(party.affiliation,'') as affiliation , ISNULL(contact_address.email,'') as email ,
ISNULL(party.subject_codes,'') as subject_codes,ISNULL(title.primary_subject_code,'') as primary_subject_code,
ISNULL(peer_review.comments,'') as comments ,ISNULL(edition.product_author_name,'') as author,
ISNULL(division_groups.division_group_desc,'')as division_group_desc
,ROW_NUMBER() OVER ( PARTITION BY party.party_id,edition.title_id ORDER BY edition.title_id) AS OCCURENCE
From Party
join contact_address on contact_address.party_id=party.party_id
join role on party.party_id = role.party_id and role.role_type='PEREV'
join edition on role.object_id=edition.title_id
join title_subject_code on title_subject_code.title_id=edition.title_id
join subject_code on subject_code.subject_code=title_subject_code.subject_code
join title on title.title_id=edition.title_id
join division_groups on edition.product_code=division_groups.product_code
Join dbo.staff_role on staff_role.object_id=edition.title_id and staff_role_type='COMMED'
JOIN dbo.Staff ON Staff.Staff_Id=staff_role.Staff_Id
JOIN peer_review on peer_review.title_id=title.title_id
where division_group_desc= 'Humanities'
)Select * from DEDUPE where OCCURENCE=1 order by party_id --title_id,party_id