ORDER BY value, not place



  • I'm trying to return an ordered column from a table.  The column I'm trying to order by is just numbers.  When I ORDER by that column, I get

    1

    10

    12

    2

    25

    3

    37

    and so on.  Anyone know if there's a proper solution to this? Or some dirty hack I can get by with?



  • By telling the database that the field is number and not string?



  • Yeah, your column is a character type, not numeric, so the DB is doing an alpha sort.

    Try:



    <font size="2">select columnName

    from tableName

    order by cast(columnName as int) asc</font>




  • or enforce '0' character padding.

    ie: 004
        010
    etc.
                                                           



  • @sao said:

    or enforce '0' character padding.

    ie: 004
        010
    etc.
                                                           

     

    Won't work unless you pad it to the maximum length (usually 255...).
    else 0100000000000000 would possibly come before 21000000000 because it starts with a lower ASCII value (despite being longer) depending on the exact algorithm used to determine order.



  • Looks like the column is a varchar of some sort... you'll have to cast
    it to an int or float (and pray someone doesn't insert something
    non-numeric that kills your query), or if its within your power, and it
    is supposed to be only numbers, re-type the column appropriately.



    Otherwise, you'll have to deal with it as is...which sucks.



  • You could alpha sort in groups starting with strings with length 1, then do length 2 and so forth.



  • Is this MS Access by any chance?

    If so: ORDER BY VAL([Field])



  • @John Smallberries said:

    Yeah, your column is a character type, not numeric, so the DB is doing an alpha sort.

    Try:



    <font size="2">select columnName

    from tableName

    order by cast(columnName as int) asc</font>




    Oops, I shoulda read that more closely before posting.  That is approx equivalent to my example.



  • @jwenting said:

    @sao said:

    or enforce '0' character padding.

    ie: 004
        010
    etc.
                                                           

     

    Won't work unless you pad it to the maximum length (usually 255...).
    else 0100000000000000 would possibly come before 21000000000 because it starts with a lower ASCII value (despite being longer) depending on the exact algorithm used to determine order.



    column width 255 it is.

    i get paid by the column width... plus by the line of code.

Log in to reply