Unpivot, Select Id & Group By

[Elena]

Hello, 

 

I am going to try to make sense and comprehensible in explaining.

 

Essentially, I am trying to pick the maximum value (per record ID) out of the entire data table with multiple columns, and put this value in each corresponding row for each ID.

 

For some reason, "SELECT ID, and GROUP BY ID"  doesn't seem to be working in Caspio. 

 

My information is below: The problem with this code is that, it is finding the max alright, but it is putting the same value into each record.

 

How can I correct this so that each record ID will have the right maximum value? 

For example:  ID 123 = $97,900,   ID 456 = $83,820   ID 789 = $59,730

 

 

Table name:  market_data

 

ID

123

456

789

 

PayscaleAverage

$89,000

$76,200

$54,300

 

ERI_Median

$97,900

$83,820

$55,321

 

Miliman_Avg 

$91,670.00

$78,486.00

$59,730.0

 

 

Here is the SQL Query: 

I am using UNPIVOT function.

 

SELECT  MAX(highestdata) AS maxnumber
FROM market_data
UNPIVOT (highestdata FOR Val IN (PayscaleAverage, ERI_Median, Miliman_Avg )) AS Number
WHERE ID = [@field:ID_Lookup]

I appreciate any help. Thank you in advance.

 

 

P.S. 

 

Please, if you could, please do not recommend conditionals, such as,  "WHEN Col1 < Col2 then..."

because this table has over 200 columns.

 

Edited February 19, 2016 by Elena

[aam82]

try

 

WHERE ID = target.[@field:ID_Lookup]

 

[Elena]

Hi there, I'll try that and post back.

[Elena]

Hello there aa82, 

 

I am happy to report back that your recommendation worked. My filter is now operating fully as intended with a simple addition or "target".  

 

Thank you so much.

 

E