How to ORDER BY lexigraphically?

[dnszero]

I'm using mysql.

SELECT * FROM mytable ORDER BY inv_num;

inv_num is an integer field. I need it to be sorted in numerical order...but it's not. If i have more than 9 items i get:
1
10
11
2
3
4
5
...

Is there anyway to get my query results sorted properly when they are returned?

[[deleted]]

"inv_num is an integer field. I need it to be sorted in numerical order...but it's not. "

then it's not an integer column. change the column type from varchar/char to integer.

[smarlowe]

Or cast it during the query. MySQL can cast can't it? I'm not sure what the syntax is, but if you can't change it to int because of things beyond your control, you can cast it for an order by. You can also cast it for indexes in many databases like postgresql. Not sure about mysql, but it's a good bet you can do that too.

[[deleted]]

MySQL does not support functional indexing, and I can't find any reference to type-casting anywhere....

[smarlowe]

Well, there ya go, another reason to go straight to Postgresql, do not pass MySQL, do no collect $200...

:-)

[[deleted]]

Couldn't agree more Scott!

although I'd like to have a short chat about that 'do not collect $200' part... 🙂

[dnszero]

Thanks for pointing that out, Vincent. It should've been obvious but I forgot that I had changed that column type a couple weeks ago.

FYI, I came up with a novel solution (as other constraints prevent me from changing the data type). My maximum value is less than 100 so I simply prepend a zero to all single digit numbers entered.

Thanks for the help, all.

[[deleted]]

That is called a workaround :-)