ORDER BY Issue

Discussion in 'MySQL' started by grutland, Mar 12, 2010.

0
  1. #1
    I'm not 100% if I'm just having a mental block or not, but I'm trying to order some results by a price value.
    The values are saved in the DB as integers with no floats or any thing else.

    What I am trying to achieve is the results ordering like:
    1, 2, 10, 20, 100, 200, 100, 200, 1000, 2000

    But what I am getting is:
    1, 10, 100, 1000, 2, 20, 200, 2000

    Any one have any ideas what I can do to solve this issue?
     
    grutland, Mar 12, 2010 IP
  2. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #2 
    SELECT * FROM TableName ORDER BY price_column ASC;
    Code (markup):
    No matter Integer or Double is the type of price_column
    Regards :)
     
    koko5, Mar 12, 2010 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    What is the actual data type of the column? If the column is an int, decimal, float or other numerical data type it should sort correctly. If you are trying to store the values in a char or varchar column you get the result you are seeing.

    You either need to alter the table to make the column a numerical data type, or use the CAST function and sort by it.

    You would do something like:

    SELECT CAST(my_column AS INT) AS converted_int FROM my_table ORDER BY converted_int ASC;

    This creates a lot of unnecessary overhead, so the best method is to switch the column's data type.
     
    Last edited: Mar 12, 2010
    jestep, Mar 12, 2010 IP
  4. grutland

    grutland Active Member

    Messages:
    86
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    71
    #4
    Yeah I think that may be the problem, I can't access my DB at the moment but fairly sure the column is a varchar.
    So I will try and use cast later on, either that or convert the column to integer as it will only ever be holding integer information.
     
    grutland, Mar 12, 2010 IP
  5. jakkevin4v

    jakkevin4v Peon

    Messages:
    13
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    in sql only two orders ascending and descending

    syntex

    SELECT column_name(s)
    FROM table_name
    ORDER BY column_name(s) ASC|DESC
     
    jakkevin4v, Mar 18, 2010 IP
  6. dropcatchsell

    dropcatchsell Active Member

    Messages:
    431
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    60
    #6
    They're not set as integers in the DB. They're being stored as strings. You need to go into the DB, and change that column to integer.
     
    dropcatchsell, Mar 19, 2010 IP