Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Getting a non-zero number is a set

    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.
    Last edited by bauhsoj; 03-08-2007 at 10:44 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Do you need your default to be 0? Could you use NULL?

    If you use NULL you could use the following:

    Code:
    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.

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •