SQL problem - 'order by' with numbers

need 1, 2, 12 not 1, 12, 2

[Trisha]

I would like to be able to order the items I pull out of the database by price so that 2 comes before 12, not the other way. Is there any easy way to do this? My PHP/MySQL book doesn't have anything about this, or at least I couldn't find it.

Thanks

[TheDave]

It's probably got to do with the way you have set up your field as a text entry rather than a number. Do a search for MySQL field type.

[woop01]

Dave is right, I've had that happen in the past. Just change the data type of the field.

[Trisha]

You are most likely right! The software I used to get the data into the database automatically assigns a datatype, and made it a text data. So what should the data type be so that the two decimal places to the right stay like they should for money? One other time I changed a price field to what I though would be the right data type and it really messed up the values.

[TheDave]

"double" or "real"

[timster]

There are a few things you can do to store currency values. Here are two ways:

Probably the easiest is to declare an INTEGER field that stores the number of cents, not dollars. When you need to present the data as dollars, of course you just divide by 100. (You may need to use MEDIUMINT or BIGINT if you are working with very large numbers.)

Or, you can just declare the field to be a FLOAT, which can handle decimals. Theoretically, FLOAT fields aren't exact values. So if a very large number is added (>1,000,000,000,000,000,000,000) you may not get an exact dollar and cent value.

You'll want to check out the field definitions for your particular flavor of SQL. If you are using MySQL, this information can be found here:

[mysql.com...]

[ikbenhet1]

after you fix the types you can just use: order by price asc
other way around: order by price desc

add it before limit if you have that in you query and after group if you have that in your query else put in in the end.

don't know if that was what you meant;

an difficult way to do the same thing:
order by price=1 desc, price=2 desc, price=12 desc

learned it on webmasterworld.

[coopster]

I have often wished that MySQL had some sort of "natural order" function built in that would allow sorting of text fields as you are describing, Trisha, but haven't discovered it nor figured it out yet. If I need to do a natural order sort I usually end up reading the result set into an array and using a PHP function, such as natcasesort [php.net] or writing my own using usort [php.net].

However, as mentioned here, there are a number of ways for Storing monetary values in MySQL [webmasterworld.com]. MySQL recommends [mysql.com] using the

DECIMAL

Numeric Type [mysql.com].

[Trisha]

Thanks everyone again for all your advice! At least one of the solutions given should work for me!