Sort text fields with numbers in natural order

[Mainer82]

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]

is there always a dash before the number?

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

[Mainer82]

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]

I hope that is clear.

not in the slightest

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

[Mainer82]

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]

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

[Mainer82]

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]

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]

Here you go.

[BrandonK]

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]

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

[Mainer82]

So you're saying
title - The Two Towers
settitle - Lord of the Rings
setnum - 2

?
ORDER BY settitle, title, setnumber?

[r937]

close

ORDER BY settitle, setnumber

[Mainer82]

Thank you Rudy, you have been very helpful. I have it working now.