How to find the lowest value from several fields and assign it to another field?

i have a table where the records are in the following fashion:

as clearly indicated, the highest category among w1,w3 and w4 makes up the class. Here "a" is regarded as the highest category and "b" the next and so on. So among the three categories, i want to take the highest category and assign it to class. I have around 2000 records to which i want an update sql statement so that the each records are assigned with proper class. At present the records in class column is empty in my table.

pls help me to achieve this

re: How to find the lowest value from several fields and assign it to another field?

SELECT LEAST(ASCII(w1),ASCII(w3),ASCII(w4)) into value FROM Table;

Then convert "value" into charcter again and assign that value to your class field.

SET class = CHAR(val);

you can do like this:
UPDATE Table SET class = CHAR(LEAST(ASCII(w1),ASCII(w3),ASCII(w4)));

Thanks,
Lauren

re: How to find the lowest value from several fields and assign it to another field?

thankyou Lauren, as your approach for the same seems to work but still i have a problem. That is, i forgot to mention that the w1, w3 and w4 can also contain NULL values. So, if i update it with the above SQL statement, some records are not assigned any class since some categories have "NULL" as values in my mysql table. So, now how should i take care of this NULL problems too?

re: How to find the lowest value from several fields and assign it to another field?

As we can assume that nothing can be greater than 'z' in the alphabet series :)

Thanks,
Lauren

re: How to find the lowest value from several fields and assign it to another field?

You don't really need the ASCII calls tho. The LEAST function compares characters just the same without them ;-)

re: How to find the lowest value from several fields and assign it to another field?

Thanks for the optimization, i became little bit more logical :-)

Thanks,
Lauren

re: How to find the lowest value from several fields and assign it to another field?

there is some problem with the sql statement cause i get the following error. Sorry, i am not good with this type of sql queries. so please help me further.

I am trying to run this query via phpmyadmin.

re: How to find the lowest value from several fields and assign it to another field?

thankyou for a change of the thread heading

re: How to find the lowest value from several fields and assign it to another field?

UPDATE Table SET class = LEAST(IFNULL(w1,'z'),IFNULL(w3,'z'),IFNULL(w4,'z') );

Sorry i forgot to put a mysterious closing bracket at the end in the above query.

This is the optimized query without using ASCII function.

Thanks,
Lauren

re: How to find the lowest value from several fields and assign it to another field?

forgot to thankyou for your help. it worked like charm .... so thankyou very much.