Numeric order when returning results from MySQL

Question

I have the following types of titles in a table in my db:

Topic 1 blah blah
Topic 2 blah blah 
Topic 3 blah blah
... 
Topic 10 blah blah
Topic 11 blah blah
etc...

The select query will always return the results like this:

Topic 1 blah
Topic 10 blah blah
Topic 11 blah 

...leaving out Topic 2, Topic 3 etc... until after all the teens will I get Topic 2 etc...

How can I get my results as:

Topic 1
Topic 2
Topic 3

.... all the way to Topic 9 and only then have Topic 10?

Answer 1

Look up the ORDER BY clause. You can say ...ORDER BY TOPIC ASC to order by the TOPIC field in ascending order (and DESC would give you descending order). Since it looks like TOPIC is an alphabetic field, you may see "Topic 10" before "Topic 2". I'm not sure if MySQL has a natural sort feature, so you may want to add a sort-value column to the table. Alternatively, sort by the topic's ID field (though that won't help if topics are not added to the database sequentially).

Answer 2

what FrustratedWithFormsDes said - also, sounds a bit as if the topic id isn't numeric but a string

Answer 3

Text is sorted that way. Since char "1" is less than char "2", "10" comes before "2". That is why you're seeing topics in this order, and not the numeric order.

What I suggest you to do is add a topic_number INTEGER field to the table, and order by topic_number.

If you can't do this, you have two possibilities:

1. Sort the topics in PHP. This is easy and fast so long as you don't have many topics.
2. Pad the topic numbers, e.g. Topic 00001 Blah; Topic 00002 Foo; etc. These are sorted in the order you desire. If you don't want to show the leading zeroes, it's easy to get rid of: preg_replace('/Topic 0+/', 'Topic ', $text);

Answer 4

You can't do this in SQL. ORDER BY can sort by alphabetical order but that'll always put Topic 10 after Topic 1. I think you'll need to create a new column with the actual order of the entries.

Answer 5

I think what William is looking for is a natural sort. I don't believe this is implemented in mysql yet.

If your data is known to be in this format, it might be easier to use substr and perhaps cast in the order by.

For example:

select title from table order by cast(substr(title, 7) as signed integer);

This is a poor solution, however, and is not flexible for other types of data. For example, a new row "Z Topics 1" would break it and any other solution posted so far. I think you either need to do external sorting in a language that supports natural sorting or add a new column specific for sorting (such as creation date)

Answer 6

This is a problem with your database design. The topic number should be stored as an integer. If you can't change the design, try this query instead:

SELECT title
FROM table1
ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' ', 2), ' ', -1)
              AS UNSIGNED);

Result:

'topic 1 foo'
'topic 2 bar'
'topic 10 baz'

Test data:

DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (title VARCHAR(100) NOT NULL);
INSERT INTO table1 (title) VALUES
('topic 1 foo'),
('topic 2 bar'),
('topic 10 baz');

Answer 7

If those records you gave are all in a single field, then the ORDER BY is doing what it does. It sorts alphabetically and alphabetically 10 comes before 2.

Your best bet is to add a "topic_id" field to your table that has the topic id in a numeric format (make sure the field is a numeric data type, like int).

Alternatively, if this is REALLY not an option and you know the format of the Title will always be the same, you could write a user defined function and then do your order by on that function instead (but this is pretty drastic. Go the new field route if at all possible).

http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html

Answer 8

I suggest that you have field topic_name with values 'Topic 1','Topic 2', ... Topic N - "Topic " + Number. To sort by this number you need to split field value

OREDER BY CAST(SUBSTRING(topic_name,7) AS UNSIGNED)