Getting a non-zero number is a set

[bauhsoj]

A table has a series of fields (e.g. id1, id2, id3, id4, etc) that can contain an unsigned integer. The default value for these fields is 0. I am trying to find some way to use built in MySQL functions to determine which is the last field in that series to have a non-zero number and return that number within the results instead of having to do it in the results using a PHP loop.

In other words, if id4 is 0 and id3 is greater than zero then I want id3 but not id2 or id1 regardless of their values, and so on and so forth.

Does anyone know a way I can do this using SQL? I have MySQL 4.1 on the server.

[guelphdad]

Do you need your default to be 0? Could you use NULL?

If you use NULL you could use the following:

select
idcolumn,
(case when coalesce(column4, column3, column2, column1) < 0
then 0
else coalesce(column4,column3,column2,column1) end) as goodvalue
from yourtable

what this will do is return the first non null from your columns in that order.
the first part says that if none of the values are above 0 then return 0. you could change this to another value if you needed to.

[bauhsoj]

Actually I wouldn't need the case check as at least one value is always non-zero, so just using COALESCE() would work if those fields defaulted to NULL. Unfortunately changing them from 0 to NULL as their default would mess up all sorts of things.