# Thread: COALESCE + Order By.. why does this happen?

1. ## 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. 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!

3. Originally Posted by longneck
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!

Thank You!

#### Posting Permissions

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