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