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
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.