SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY problems depending upon type

    I want my query to ORDER BY number, but I have a problem...

    I have the following numbers...

    1
    4
    22
    28T

    I my number column is of type int, I lose the "T" in 28T.

    If I make it of type CHAR, it orders it as follows:

    1, 22, 28T, 4

    4 should be before 22. Can anyone help with this? One way I can get it to work is to keep it as a CHAR and enter 1 and 4 as 01 and 04, but i don't want to have to do that.

    Anyone?

  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)
    Code:
    ORDER BY CAST(colname AS UNSIGNED)
    note that NUMBER is a reserved word and you should probably change the column name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    If you want to treat it like a numeric, then the field should be numeric. That means whatever situation makes the T in the field should be removed. If you've got multiple sub-groups under one number, make it an additional field, then sort it by both of them.

    In other words, change your structure to be like this:

    OrderByField int,
    OrderBySubField char

    Then your order by portion of the SQL statement would be ORDER BY OrderByField, OrderBySubField
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    ORDER BY CAST(colname AS UNSIGNED)
    note that NUMBER is a reserved word and you should probably change the column name
    Won't the cast blow out on the 28T, though? Curious minds want to know....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    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 DaveMaxwell View Post
    Won't the cast blow out on the 28T, though? Curious minds want to know....
    really curious minds would run a quick test...
    Code:
    SELECT CAST('28T' AS UNSIGNED)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Good thing I'm not really curious then, cause unsigned doesn't work in SQL Server 2005.

    Msg 243, Level 16, State 1, Line 1
    Type UNSIGNED is not a defined system type.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    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)
    oh, now i'm embarrassed, because CAST AS UNSIGNED is mysql syntax

    the original poster did not indicate which database system this is, so you shouldn't assume sql server either

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

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    oh, now i'm embarrassed, because CAST AS UNSIGNED is mysql syntax

    the original poster did not indicate which database system this is, so you shouldn't assume sql server either

    I didn't, which is why I answered the OP's question the way I did.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  9. #9
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using mysql

  10. #10
    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 menuserve View Post
    I'm using mysql
    we have a separate forum for mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    Thank you, that worked perfectly. It properly orders a VARCHAR column that has numbers and letters in it!


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
  •