SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2005
    Location
    uk colchester
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY name ASC problem

    I have just created a database where the info gets input via an online form, I have a column called membershipnumber, this is my sql line:

    Code:
    SELECT membershipnumber, title, firstname, surname FROM members2006 ORDER BY membershipnumber ASC
    it was coming out in ascending order ok until entry number 9, when i entered entry number 10 this was positioned between 1 & 2, the Mysql column was created as a text column but will only have numbers entered in it, so why does it see 10 as less than 9, i tried 10. with same resilt.
    Please help I am new to this.

    Regards

    Ian

  2. #2
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's normal.

    1
    10
    2
    ...
    9


    and so on is how char fields are sorted. you need to cast the field as an int.

    if you're using mysql 5:

    Code:
    SELECT cast(membershipnumber AS int), title, firstname, surname FROM members2006 ORDER BY membershipnumber ASC

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    almost, but not quite, brian:
    Code:
    SELECT membershipnumber
         , title
         , firstname
         , surname
      FROM members2006
    ORDER
         BY cast(membershipnumber AS int) ASC
    meanweaver- if your table is going to have a significant amount of rows, this is a bad idea as sorting will not use an index. you should instead convert the column to a numberic data type.
    Last edited by longneck; Jan 31, 2006 at 19:06.

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2005
    Location
    uk colchester
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am on a shared server and using something less than mysql5, what i did was on my pc was drop the column membershipnumber and then added it back in as an INT, now it seems to work fine, did try the cast code as above but got the not known syntax error as i guess thats only good for mysql 5,
    anyway i could just delete the column as i done on my pc, but is there a way to just alter that column to change it from TEXT to INT,

    Thanks for your advice gents will get better at this in time i hope.

    Regards

  5. #5
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    almost, but not quite, brian:
    oops. yup. thanks for the correction

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    alter table members2006
    change membershipnumber
    membershipnumber int;


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
  •