Using second highest value in an ON clause-CodePudding

I have an existing MSSQL view where I need to include a new join to the view. To get the correct record data I need to select the entry where the ActivityKey is the second highest (essentially the second most recent revision of the policy).

select 
...
from polmem a
left join polMemPremium wpmp on (wpmp.policyNumber=pf.sreference
and wpmp.lPolicyMemberKey=a.lPolicyMemberKey 
and wpmp.lPolicyActivityKey = (select Max(wpmp.lPolicyActivityKey) where wpmp.lPolicyActivityKey 
NOT IN (SELECT MAX(wpmp.lPolicyActivityKey))))
where 
...

But the above results in this error:

An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

Essentially the error is telling me I need to have the aggregate

(select Max(wpmp.lPolicyActivityKey) where wpmp.lPolicyActivityKey NOT IN (SELECT MAX(wpmp.lPolicyActivityKey)))

in a Having and then list most if not all of the columns in the view's Select statement in a Group By. My issue is as this is a view used in multiple places and doing what MSSQL wants is a massive change to the view for the sake of what I thought would be a relatively simple addition. I'm just wondering if I'm approaching this wrong and if there is a better way to achieve what I want?

CodePudding user response：

Just try something like:

select ...
from .....
..........
cross apply (select 
               *
               ,row_number() over (order by wpmp.lPolicyActivityKey desc)
             from web_PolicyMemberPremium wpmp
             where wpmp.policyNumber=pf.sreference 
             and wpmp.lPolicyMemberKey=a.lPolicyMemberKey) wpmp
....
where ...
and wpmp.rn = 2

I added cross apply (that means there should be a policy in the table otherwise the rows will be excluded). You could put an outer apply and change the where clause isnull(wpmp.rn,2) = 2 or similar .. but it doesn't make much sense to me.

PS. It would help a lot us (and mostly you) if you format the code in a nice manner.