Selecting Max Value, However Prioritising Certain Values

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.

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;