Skip to content
Advertisement

Sql join only 1 row

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
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement