ORDER BY problem in MYSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

ORDER BY problem in MYSQL

Post 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?
User avatar
haagen
Forum Commoner
Posts: 79
Joined: Thu Jul 11, 2002 3:57 pm
Location: Sweden, Lund

Post 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.
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

Post by Matt Phelps »

Actually the field type is a decimal type.

Are you saying I can use the OR logic in a query?
fatalcure
Forum Contributor
Posts: 141
Joined: Thu Jul 04, 2002 12:57 pm
Contact:

Post 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.
Post Reply