views:

61

answers:

8

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?

A: 

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).

FrustratedWithFormsDesigner
A: 

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

Nicolas78
Correct - this is what happens when ordering by text. So, how would you fix that?
OMG Ponies
@OMG Ponies: I don't think MySQL has a "natural" sort feature, so a special sort-value column could be added.
FrustratedWithFormsDesigner
well change the ID field to INT
Nicolas78
Yes, but how using MySQL functionality? MySQL does implicitly convert strings to INTs...
OMG Ponies
Ah ok, I wasn't aware that "Topic 10" was the content of the field. I thought we had a text field with numeric contents.
Nicolas78
+1  A: 

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);
jmz
If php is an option, I would highly recommend using natsort.
Brandon Horsley
A: 

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.

rhinoinrepose
A: 

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)

Brandon Horsley
I see others have come up with the same solution, but it should be noted that this only works if your titles all start the same. Otherwise, this is definitely the wrong solution. What you really need is a natural sort, and I don't think that is available for mysql - I would recommend sorting externally if possible.
Brandon Horsley
@Brandon: See Mark Byers for an example that starts the substring based on the space between the title and the number.
OMG Ponies
+1  A: 

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');
Mark Byers
`ORDER BY RIGHT(title, 2)`, but the risk is if a title can have a three (or more digit numeric value)...
OMG Ponies
A: 

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

Zippit
A: 

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)
a1ex07
+1: Only issue is where to start the substring. `ORDER BY RIGHT(title, 2)` is another alternative.
OMG Ponies
what a lame solution
Col. Shrapnel