Order by Numeric decimals then Alpha

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wpdfran
Forum Newbie
Posts: 1
Joined: Mon Aug 25, 2014 10:59 am

Order by Numeric decimals then Alpha

Post by wpdfran »

I am trying to get the decimal numbers to order correctly.

Here is my code:

$sql = "(SELECT * FROM locations WHERE X REGEXP '^[0-9]' ORDER BY cast(X as decimal), X, Y)
UNION ALL
(SELECT * FROM locations WHERE X REGEXP '^[a-z,A-Z]') ORDER BY X, Y";

Right now, numbers on top but not in correct order, Alpha on bottom is in order
1, 1.5, 10, 10.5, 18, 2, ADMIN, CAFE, CDC, CMAT

I need:
1, 1.5, 2, 10, 10.5, 18, ADMIN, CAFE, CDC, CMAT
phpdeveloper1
Forum Newbie
Posts: 19
Joined: Tue Aug 12, 2014 6:13 am
Location: Chennai, India

Re: Order by Numeric decimals then Alpha

Post by phpdeveloper1 »

wpdfran wrote:................ cast(X as decimal), X, Y)
.................
Have you tried giving the decimal precision and scale ?

(http://dev.mysql.com/doc/refman/5.0/en/ ... types.html)
Chris, Php Developer and Programmer,
https://www.phpfreelanceprogrammer.com/
Post Reply