SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    COALESCE + Order By.. why does this happen?

    Hi Everyone.

    I have a question more to serve my curiosity/understanding. I have a MySQL statement that does some calculations on the fly, and I am using the coalesce function to return a 0 if there are no records.

    Everything works, however I want to sort it in Descending order and only show the top 10. This kind of works, but it doesnt interpret my data right.

    For example, lets say I have these values:
    2
    3
    4
    5
    6
    7
    8
    9
    10
    22
    30

    It will sort it in a fashion where it thinks 9 is the highest number, 8 is the second highest, and so on. It seems to only look at the first number of a double digit number. The column data type is INT(11).

    Here is my command:
    Code:
    SELECT p.id
        , p.player
        , p.team
        , p.mugshot
        , coalesce(sum(s.goals),'0') as goals
        , coalesce(sum(s.assists),'0') as assists
        , coalesce(sum(s.fouls),'0') as fouls
        , coalesce(sum(s.assists + s.goals),'0') as points
        , t.teamname
    FROM league as p
    LEFT OUTER JOIN (SELECT goals
            , assists
            , fouls
            , player_id
        FROM stats) as s
        on s.player_id = p.id
    LEFT OUTER JOIN (SELECT id
           ,teamname
         FROM teams) as t
         on t.id = p.team
    GROUP
        BY p.id
        , p.player
    ORDER BY points DESC
    LIMIT 0,10
    If I remove the COALESCE it works fine, but I like having it there incase there are nulls.

    If anyone can help it would be appreciated.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    take the quotes off the zeroes. a number inside quotes is a string and forces the entire column to sort as a string. a number outside of quotes is... (wait for it....) a number!
    Last edited by longneck; Nov 19, 2008 at 14:55.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    take the quotes off the zeroes. a number inside quotes is a string and forces the entire column to sort as a string. a number outside of quotes is... (wait for it....) a number!

    *hangs head in shame*.

    Thank You!
    Last edited by longneck; Nov 19, 2008 at 14:55.


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
  •