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.