Find max value by looking at several columns

I have the following fields in a record. I have a script that will find the max number to the right of the colon. But also want the corresponding zcode to go with it. Below, column Z4 has the max value of 1. So, I want to update the final Zcode field with "z184".

IDZ1Z2Z3Z4Z51z433:0.41z151:0.67z149:0.52z184:1z392:0.5

So, here is the script that I am using to find the column that has the highest score to the right of colon.

SELECT MAX(SUBSTRING(maxzcode,CHARINDEX(':',maxzcode)+1,LEN(maxzcode))) MaxZcode, ID FROM table1 UNPIVOT (MaxZcode FOR E IN (Z1, Z2, Z3, Z4, Z5) )AS unpvt GROUP BY ID;

Thanks!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

red68 commented · Jan 15, 2019 at 12:58 AM

Here is image of table.

0 ·

table.jpg (12.5 KiB)

red68 commented · Jan 16, 2019 at 10:08 PM

What if we find max value of all Z columns using score to the right of colon and then return the full string. So, below, I would return value for Z4 which is: z184:1. Thanks!

0 ·