I want to join the table CustomerAgreementRole with only one row from AgreementRoleGroup, based on ViewPriority. Example: CustomerAgreementRole can have three rows. I only want the joined row where ViewPriority is highest
x
AgreementId, AgreementRoleId
1 1
1 2
1 3
CustomerAgreementRole
1. AgreementId
2. AgreementRoleId
AgreementRoleGroup
1. AgreementRoleId
2. ViewPriority
Current query:
select * from CustomerAgreementRole car
join (select agreementRoleId, min(ViewPriority) as mi from AgreementRoleGroup group by AgreementRoleId) as arg on car.AgreementRoleId = arg.AgreementRoleId
Expected result:
AgreementId, AgreementRoleId, ViewPriority
1 1 1
Advertisement
Answer
Made it working with this:
WITH CTEViewPriority (lowestViewPriority, agreementId) as (
select min(ViewPriority), AgreementId from CustomerAgreementRole as car
left join AgreementRoleGroup arg on car.AgreementRoleId = arg.AgreementRoleId
group by agreementId
)
select * from CustomerAgreementRole as car
inner join CTEViewPriority as arg on lowestViewPriority = arg.agreementRoleId and arg.agreementId = car.agreementId