Sort text fields with numbers in natural order

**Mainer82** · Nov 13, 2009, 08:45

I have a table that has a field that contains text and then numbers (item-1, item-10) however sorting it alphabetically is a problem as it sorts by item-1,item-10,item-2, etc.

I tried: ORDER BY c_title + 0 ASC; but that does not sort everything alphabetically.

**r937** · Nov 13, 2009, 09:13

is there always a dash before the number?

Code:

ORDER BY CAST(SUBSTRING_INDEX(c_title ,'-',-1) AS UNSIGNED)

**Mainer82** · Nov 13, 2009, 10:46

I decided to take a different, route but ending up with the same problem.

I have a table with a list of books (example):

1984
Charolets Web
Animal Farm
Wired
Lord of the Rings: Fellowship of the Ring
Lord of the Rings: Return of the King
Lord of the Rings: The Two Towers

The table as a field of title and setnumber to identify a book that is a part of a set, so LOTR would have 1, 2 and 3.

I want to order the title by alpabetical order AND by set order.
So it should look like this:
1984
Animal Farm
Charolets Web
Lord of the Rings: Fellowship of the Ring (being 1)
Lord of the Rings: The Two Towers (being 2)
Lord of the Rings: Return of the King (being 3)
Wired

This is the query I'm trying to run:
SELECT c_title, c_bsetnum FROM `catalog`ORDER BY c_bsetnum +0 ASC , c_title ASC;

This is what I get:
1984
Animal Farm
Charolets Web
Wired
Lord of the Rings: Fellowship of the Ring (being 1)
Lord of the Rings: The Two Towers (being 2)
Lord of the Rings: Return of the King (being 3)

Which is not what I want for result. I hope that is clear.

**r937** · Nov 13, 2009, 10:58

Originally Posted by ogden2k

I hope that is clear.

not in the slightest

what happened to item-1, item-2 and item-10 ???

**Mainer82** · Nov 13, 2009, 11:10

Sorry, I decided to go another route and got rid of the -1, -2, etc. That -1 was appened to end of the book title and looked messy so I created a field for that purpose, c_bsetnum.

**r937** · Nov 13, 2009, 11:15

Code:

SELECT c_title
     , c_bsetnum 
  FROM `catalog`
ORDER 
    BY c_title
     , c_bsetnum

**Mainer82** · Nov 13, 2009, 11:29

This is what I get:
1984
Animal Farm
Charolets Web
Lord of the Rings: Fellowship of the Ring (being set 1)
Lord of the Rings: Return of the King (being set 3)
Lord of the Rings: The Two Towers (being set 2)
Wired

It should look like this:
1984
Animal Farm
Charolets Web
Lord of the Rings: Fellowship of the Ring (being 1)
Lord of the Rings: The Two Towers (being 2)
Lord of the Rings: Return of the King (being 3)
Wired

**r937** · Nov 13, 2009, 11:38

do me a favour, dump your table (you can use mysqldump if necessay), then show the CREATE TABLE statement as well as the three lord of the rings INSERT statements

**Mainer82** · Nov 13, 2009, 12:00

Here you go.

**BrandonK** · Nov 13, 2009, 13:04

How do you relate the LOTR books together. Besides c_bsetnum (which I take is the order within the set), how do you differentiate the LOTR series versus the Harry Potter series?

**r937** · Nov 13, 2009, 13:39

okay the answer was obvious all along, but somehow i failed to see it

what you had was a sort by title as the first key

that way, the three Lord of the Rings come after Charolets Web and before Wired

and (here's the key point) they are also in sequence by their [sub]titles, but since each title is different, those are the only numbers within each title (if you know what i mean)

so adding a sequence column to title doesn't work

what will work is if the table had an additional column: set title

then for books which aren't in a set, just populate this with the actual title

for books that are part of a set, it's the set title, e.g. Lord of the Rings

then use ORDER BY settitle, setnumber