Order by numeric values in SQL

Aug 11 2005

I didn’t know how to do this before today, so perhaps it might be of some use to someone else.

Normally a SQL query “ORDER BY fieldname” will do a straightforward string comparison; fine if you want to list items alphabetically, but not so good if you’re comparing numeric values, as you will end up with a list in non-numeric order, for example:

The values are ordered alphabetically, so 123 comes before 2 – far from ideal.

To order the values as they should be, simply use the SQL ABS function, like so:

SELECT name, price FROM products ORDER BY ABS(price) ASC

Voila – instant numerical ordering!

Filed under: MySQL.

Digg this article

Bookmark this article with del.icio.us

Previously: Hiding default links on the Textpattern 'write article' page

Next: Build it and they will come - how to promote your site online


Comments

Chris
1139 days ago
NICE!

I was wondering how to do just that!

Thanks.
#1
Mark James
1131 days ago
Not normally one to rain on other people’s parades, but your example will have an unexpected side effect when your values are negative (they will be converted to positive by the ABS() function).

I believe the recommended way to achieve sorting textual columns in to use the SQL CAST function.

CAST( field as data-type )

Which, for a MySQL database, would give:

SELECT name, price FROM products ORDER BY CAST(price AS SIGNED) ASC

Oracle and other SQL databases should behaive similarly, just a quick heads-up for anyone looking for the solution to this problem.
#2
Matthew Pennell
1131 days ago
Excellent, thanks for the clarification/correction, Mark – obviously negative values are not normally an issue when talking about product prices, but it’s much better to have the complete picture (from someone who obviously knows more about SQL than I do!)

:)
#3
Ercu
1089 days ago
Thanks , thanks and thanks once more
#4
Andy Moore
976 days ago
Thanks very much, just what I wanted.

Nice looking site by the way, your live preview feature is cool too :)
#5
Jister
941 days ago
You are the greatest. I’ve never heard of this function and it is exactly what I was looking for. Helped remove my headache.
#6
Jeff Cartwright
939 days ago
I put off ordering numeric lists because I didn’t know how to do it, and couldn’t find it in the MySQL manual. Thank you very much.

Yesterday, I added user notes (employee only for now) to my site. Your site puts me to shame.
#7
Ed
913 days ago
Note to anyone that is trying to do this on reverse dns records, if MySQL is giving you warnings when running your query try it like this:

SELECT * FROM `table` ORDER BY SUBSTRING_INDEX(`name`, ’.’, 1) CAST AS SIGNED ASC;

Hope this helps somebody out there.
#8
rahmah
887 days ago
How I want to use order by if my data is like this :-
Group 1
Group 10
Group 11
Group 12
Group 13
Group 14
Group 15
Group 16
Group 17
Group 18
Group 19
Group 2
Group 20
Group 3
Group 4
Group 5
Group 6
Group 7
Group 8
Group 9
Test Group

I want the output like this
Group 1
Group 2
Group 3
Group 4
Group 5…..

Please advice
#9
Matthew Pennell
886 days ago
rahmah: I don’t know how you would do that, to be honest; your best option would be to alter the way the data is stored so it is just a numeric entry in a field named ‘Group’.

Failing that, I guess you could get the array in PHP, strip out the “Group ” bit and then sort it.
#10
Nancy
881 days ago
Thanks so much, you just saved my ass!
#11
jb
800 days ago
I was trying to do that in Sql CE and i couldn’t use the cast operator, i think it is not supported. Well, the ABS solution did the trick…

Thanks a lot
#12
Maxwell Lamb
779 days ago
Or , perhaps, just set the column’s datatype to a numerical format.

Just my $0.02.
#13
John Hansen
720 days ago

Nice!

I use the following method:
SELECT name, price FROM products ORDER BY price + 0

rahmah: I know I’m late, but try this:
SELECT groupnames, substring(groupnames, 7) AS groupnumber ORDER BY groupnumber + 0

#14