SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
-
Nov 13, 2009, 08:45 #1
- Join Date
- Aug 2003
- Location
- Maine, USA
- Posts
- 250
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Sort text fields with numbers in natural order
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.Get to know the man behind the screen
-
Nov 13, 2009, 09:13 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
is there always a dash before the number?
Code:ORDER BY CAST(SUBSTRING_INDEX(c_title ,'-',-1) AS UNSIGNED)
-
Nov 13, 2009, 10:46 #3
- Join Date
- Aug 2003
- Location
- Maine, USA
- Posts
- 250
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Get to know the man behind the screen
-
Nov 13, 2009, 10:58 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 13, 2009, 11:10 #5
- Join Date
- Aug 2003
- Location
- Maine, USA
- Posts
- 250
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Get to know the man behind the screen
-
Nov 13, 2009, 11:15 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:SELECT c_title , c_bsetnum FROM `catalog` ORDER BY c_title , c_bsetnum
-
Nov 13, 2009, 11:29 #7
- Join Date
- Aug 2003
- Location
- Maine, USA
- Posts
- 250
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
WiredGet to know the man behind the screen
-
Nov 13, 2009, 11:38 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Nov 13, 2009, 12:00 #9
- Join Date
- Aug 2003
- Location
- Maine, USA
- Posts
- 250
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Here you go.
Get to know the man behind the screen
-
Nov 13, 2009, 13:04 #10
- Join Date
- Dec 2005
- Posts
- 982
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
MySQL v5.1.58
PHP v5.3.6
-
Nov 13, 2009, 13:39 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Nov 13, 2009, 14:18 #12
- Join Date
- Aug 2003
- Location
- Maine, USA
- Posts
- 250
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So you're saying
title - The Two Towers
settitle - Lord of the Rings
setnum - 2
?
ORDER BY settitle, title, setnumber?Get to know the man behind the screen
-
Nov 13, 2009, 14:31 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
close
ORDER BY settitle, setnumber
-
Nov 13, 2009, 15:22 #14
- Join Date
- Aug 2003
- Location
- Maine, USA
- Posts
- 250
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you Rudy, you have been very helpful. I have it working now.
Get to know the man behind the screen
Bookmarks