Page 1 of 1

ORDER BY problem in MYSQL

Posted: Tue Jul 30, 2002 3:34 pm
by Matt Phelps
I have some interger data that I want to display in a table and order in ASC order. The problem is that because some of the values are zero they come first in the table when really I want the first value in the table to be a 1. This is because although zero is less than 1 I don't want it to be the 'top' number (this data makes up a sort of leaderboard and zero means that the person hasn't scored yet but does exist, and obviously they shouldn't come above someone who HAS scored.)

Now I could say WHERE data>'0' ORDER BY data but I still want to display the zero values - just below all the others.

What I think I really need is the correct syntax to say:

WHERE data> = 0' ORDER BY something else OR WHERE data>'0' ORDER BY data.

To put it another way what I want to achieve is a list ordered like the following:

1
2
5
7.8
8.3
10.
11
15
0
0
0
0

Can this be done somehow with MYSQL?

Posted: Wed Jul 31, 2002 8:27 am
by haagen
What field type is data?

By your query, it looks like it is a char och varchar field. I have the experience that comparations like <, >, != (numeric) doesn't work when you have a varchar/char field. The "ORDER BY " clausul will list it in alpabetical order "DESC" reverting.

So I think the output look just fine!

Tip change field to int.

Posted: Wed Jul 31, 2002 8:41 am
by Matt Phelps
Actually the field type is a decimal type.

Are you saying I can use the OR logic in a query?

Posted: Wed Jul 31, 2002 10:23 am
by fatalcure
no, i dont think what you want can be done in one query, you might have to run 2 queries, one for everything above 0, and one for everything that = 0.