[Resolved] Ordering by a decimal number in MySQL

danj

Hey guys, first time here - I'm sure it wont be the last!

I'm currently working on a project which needs to display items from a database in the order of price.

It all works fine with numbers from £1.00 to around £9,999.99, however, when it crosses the £10,000.00 barrier it thinks its less than £9,999.99 and so shoots to the bottom of the list when its ordered from highest to lowest.

Just wondering if anyone else has come across this and managed to find a solution?

The query I'm using at the moment is:

$query = "SELECT * from preowned ORDER BY 'price' DESC";

mrhappiness

I don't believe that's the statement you use because this statements sorts by the string price and not by the column called price.
Or do you use backticks `and njot ' like in your example?

anyway:

SELECT * from preowned ORDER BY price DESC

should work just fine in case price is no varchar column but a float or another numeric type?

Drakla

Wow, well spotted Mr H, that's the kind of error thing that can have a man quivering under a table trying to bite his own eyes in frustration.

danj

Thank you for your suggestion, you're right it should have been backticks (`) and not quotes, I'm using a Mac at the moment and I couldn't find the damn key!

I've tried what you said but unfortunetly it's still shoving 10,000.00 to the bottom of the list. I have a feeling it just sees the '1' from 10,000.00 and it thinks its less than say '4' from 4,000.00, but completly disregards the the second zero from 10,000.00.

So I'm guessing that the obvious solution would be to put leading zeros infront of the items that are less than 10,000.00. But that wouldn't really scan well on the page.

Do I perhaps need to change the type of field? As its currently set to TEXT. I've played around with different types, but to no avail. I apologise, I'm quite new to MySQL and the like.

Wow, well spotted Mr H, that's the kind of error thing that can have a man quivering under a table trying to bite his own eyes in frustration.

That along with a combination of a slow ISDN line & browser caching!!

mrhappiness

Originally posted by mrhappiness
anyway:
SELECT * from preowned ORDER BY price DESC
should work just fine in case price is no varchar column but a float or another numeric type? [/B]

FLOAT
DOUBLE
DECIMAL

all should do

Drakla

Hi again.

I would actually recommend AGAINST using float or double for money columns as I've had them cause errors in sorting and comparisons on values which are actually output as being visually equal, e.g. 10.91 and 10.91 but are represented internally as one less than the other. So when I tried to find something that cost 10.91 neither of them appeared.

So do use decimal, or INT and save everthing as pence and do a number format on it.

danj

Apologies for not getting back sooner - I've been snowed under recently.

After a bit of tinkering about I used INT and converted the entries to pence as suggested by Drakla - with the php function, number_format.

Works a treat! Cheers guys - your input was v.much appreciated. 😃