SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict Mainer82's Avatar
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Mainer82's Avatar
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ogden2k View Post
    I hope that is clear.
    not in the slightest

    what happened to item-1, item-2 and item-10 ???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict Mainer82's Avatar
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT c_title
         , c_bsetnum 
      FROM `catalog`
    ORDER 
        BY c_title
         , c_bsetnum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict Mainer82's Avatar
    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)
    Wired
    Get to know the man behind the screen

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict Mainer82's Avatar
    Join Date
    Aug 2003
    Location
    Maine, USA
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here you go.
    Attached Files Attached Files
    Get to know the man behind the screen

  10. #10
    SitePoint Guru
    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict Mainer82's Avatar
    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

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    close

    ORDER BY settitle, setnumber
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict Mainer82's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •