SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast 1magic's Avatar
    Join Date
    Jan 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    column type changes in query result

    Hi everybody,
    i'm didn't find it in my mysql reference ( at least for now ) so asking here...

    I want to change the type of a result column of a query in mysql.

    Basicly I have such a query:
    Code:
     SELECT ( column1 / column2 ) AS result 
    FROM table
    ORBER BY result DESC
    Looks fine, but on a row where both columns have value of 0 (zero) the 'result' returns NULL , thus ordering it before the others with numeric value in 'result'.

    to be exactly clear:
    now it returns like this:

    result
    ---------
    NULL
    NULL
    7.00
    6.45
    6.30
    5.30 and so on...

    I want to have such results:

    result
    ---------
    7.00
    6.45
    6.30
    5.30
    NULL
    NULL

    I could use some expert help here . Thanks in advance...
    You will appreciate Bulgaria's dramatic mountains, haven-like monasteries, Roman and Byzantine ruins, and the excellent coffee you'll be offered wherever you go! -- read more!

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Needham, MA
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Use the CAST or CONVERT function

    Use the CAST or CONVERT function, or just add the calculated result to zero and it should remain an integer.

    Code:
    SELECT (0 +  (column1 / column2) ) AS result
    FROM table
    ORBER BY result DESC
    See http://www.mysql.com/doc/en/Cast_Functions.html for further reference.
    Tom Mollerus
    tmollerus@oneworkspace.com
    http://www.oneworkspace.com The affordable, simple, and secure way to manage your projects online

  3. #3
    SitePoint Enthusiast 1magic's Avatar
    Join Date
    Jan 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nope. still doesn't work. the null value in RESULT is still before the other values in DESC. Also my host has mysql 3.23 which doesn't support CAST function
    You will appreciate Bulgaria's dramatic mountains, haven-like monasteries, Roman and Byzantine ruins, and the excellent coffee you'll be offered wherever you go! -- read more!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you want nulls to come last, you have to add another sort column

    if you don't mind nulls showing as zero, then they will come last if you write
    Code:
     select case
    		 when column1=0 or column2=0 then 0
    		 else column1 / column2
    	   end as result 
      from yourtable
    order by result desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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