correct ORDER BY

jonzy

I'm using AVG in a mysql query and then using ORDER BY to put them in Ascending order but it does not seem to work as I would hoped on these numbers...

My output is this:

13.00
2.33
3.40
3.80
4.00
4.67
4.75
5.25

Is there a way to have mysql sort based on the number and decimal points so it works correctly?

thanks

jonzy

here is my query just in case you are wondering:

"SELECT order.id FORMAT( AVG( order.weights ),2) AS weight_avg FROM order GROUP BY order.id ORDER BY weight_avg"

mikell

Format() returns a string...that's why its being ordered that way. If you want to
order it numerically then make sure the value you're ordering by is a number.

For example:
SELECT CAST(FORMAT(AVG(order.weights),2) AS decimal(9,2)) AS weight_avg FROM test ORDER BY weight_avg

Basically just make sure weight_avg is numeric.

jonzy

mikell wrote:
Format() returns a string...that's why its being ordered that way. If you want to
order it numerically then make sure the value you're ordering by is a number.

For example:
SELECT CAST(FORMAT(AVG(order.weights),2) AS decimal(9,2)) AS weight_avg FROM test ORDER BY weight_avg

Basically just make sure weight_avg is numeric.

Thanks, I tried this and am now receiving an error...

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'decimal(9,2)) AS weight_avg

I did notice this when I went searching: The DECIMAL type is available as of MySQL 5.0.8.

My host has 4.1.22 🙁

Any other way to convert?

Piranha

If your host have an old version (PHP, MySQL, whatever else) you should change host. They should use whatever you need, you should not have to compromize and develop workarounds to suit them.

mikell

I agree with Piranha...screwing around with mysql compatibility issues is not my idea of a good time. Here's my last piece of advice on this...try using numeric(), float() or any other "numeric" type instead of decimal and you might get lucky.