SQL Order

[martialdc]

Hi Im using this sql code to arrange the item ascendingly;

"Select * Table1 Order By ItemCode"

but it display like this..

1
10
2
3
4
5
6
8
9

what code should i write to make it like this?

1
2
3
4
5
6
8
9
10

[KhalidMughal]

If you are using ORACLE you need to write the query in the following way:

"Select * Table1 Order By to_char(ItemCode,'9999')"

if your are using ACCESS:

"Select * Table1 Order By format(ItemCode,'0000')"

[RobDog888]

If using SQL Server then...

"SELECT * FROM Table1 ORDER BY CAST(ItemCode AS numeric) ASC"

[szlamany]

And if you cannot guarantee that this field (obviously defined as character) always has numbers in it - use:

SELECT * FROM TABLE1 ORDER BY RIGHT('00'+ITEMCODE,2) ASC

[vb_dba]

Or you could change the ItemCode field to a numeric field.