SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast petersj's Avatar
    Join Date
    Aug 2002
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY problem

    I have got a list of items with a reference that I wish to display in order of that ref. The problem is that the references get displayed in this order:

    10a
    10b
    8
    9

    I want them to be displayed in this order:

    8
    9
    10a
    10b

    Can I do something clever to achive this?

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You haven't posted any code so I'm only guessing here.

    Your query should be changed to look something like this:

    SELECT * FROM table_name ORDER BY field_name ASC

    With emphasis on the ASC at the end [img]images/smilies/smile.gif[/img]

    Hope this helps.

    -Sam
    Sam Hastings

  3. #3
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually, because this is a text field, the ORDER BY clause won't work in that manner. It sorts everything alphabetically, so 1 comes before 8, etc... Any way you can make it a numerical field and drop the "a" and "b" out, maybe storing them in a different field and concatenating them on the end...?

  4. #4
    SitePoint Enthusiast petersj's Avatar
    Join Date
    Aug 2002
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Iím using ORDER BY ref_column ASC. The problem is the mix of integers and characters. I want the item with a ref. 10a to appear after an item with ref. 9

  5. #5
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this (might be a lot of work, though, I don't know...):

    1) split the ref_column into two fields: ref_column_int and ref_column_char
    2) SELECT * from ... ORDER BY ref_column_int, ref_column_char
    3) when you output everything, just output the ref_column_int and ref_column_char back to back...

    In the form they're currently in, you won't be able to order them in the way you're trying to, because it's going to always order them alphabetically.


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
  •