SQL Query Problem: Order By not working as expected

Gnarled

For internal use at the company I work for, I have created a Top 25 Products Sold list. The SQL Query is set to select the 25 products with the highest number sold. The query is told to order the results by the number sold, and in descending order.

Unfortunately, when I display the results of the query, for some reason the number 8 is considered to be greated than 50, for example. So when I display the Top 25 products, a product which has sold 8 is listed before a product which has sold 50, or 79, etc.

What it looks like it is doing, is sorting only by the first number in the num_sold field in the database, so the first digit in the number is the only one considered.

Does anyone have any idea what the problem is?

Thanks very much.

planetsim

can you show us your query

Gnarled

Sure:

SELECT name, num_sold FROM products WHERE num_sold > '0' Order By num_sold DESC LIMIT 25

Arc

one thing is you are syaing 0 is a string by putting " ' " marks around it... but that's probably no the problem.

Are you sure its in the SQL and not in how you are displaying the results?

Show the code you are suing to display the results..

Mordecai

Your query should look something like this:
SELECT name, num_sold FROM products WHERE num_sold > 0 ORDER BY num_sold DESC LIMIT 0,25
I saw: quotes around an INT field and a missing statement for LIMIT.

Try that, if it doesn't work, try posting what it does return.

Gnarled

Originally posted by Arc
one thing is you are saying 0 is a string by putting " ' " marks around it...

You made me think, by saying this, that I may have mis-labeled the field in the database as VarChar instead of Int. Sure enough, num_sold was VarChar and not an Int. When I changed it over to an Int, everything began working properly.

Silly me 🙂 Thanks to everyone for helping me solve my problem!