I have three tables that are joined. TableA has unique values for Column1 (ID) which joins on TableC on Column1 which has non-unique values. I’m currently joining these based off the max value for Column2 in TableC. The returning a value in TableB which is simply joined off TableC.
However I want to adjust this so that if TableB.Column2 has any value greater than 0 in TableC.Column2 then this is chosen as the max value, if it is 0 then the max value is chosen normally based off numeric value.
The current query I have is this:
Select [TableA].Column2, FIRST_VALUE([TableB].Column2) OVER (PARTITION BY [TableA].Column2 ORDER BY MAX([TableC].Column2) Desc) From [TableC] Left Join [TableA] On [TableA].Column1 = [TableC].Column1 Left Join [TableB] On [TableB].Column3 = [Table3].Column3
What I am expecting to happen is that if:
TableC.Column2 > ‘0’ where TableB.Column2 = ‘KEYVALUE’ then show Table.Column2 based off TableC.Column3, however if TableC.Column2 = ‘0’ where TableB.Column2 = ‘KEYVALUE’ then show result of [TableB].Column2 based off MAX [TableC].Column2
Sample Data:
Example Output:
S7000,KEYVALUE
S6500,OTHERVALUE1
Hope that all makes sense, thank you.
Advertisement
Answer
I find your conditions hard to follow, but you seem to want apply
:
Select a.*, bc.column2 From a outer apply (select top (1) b.column2 from c join b on c.column3 = b.column3 where c.column1 = a.column1 order by (case when c.column2 > 0 and b.column2 = 'KEYVALUE' then 1 else 2 end), c.column2 desc ) bc;