sorting problem

[adnanmohd]

Hello everyone,
i have a table with customer on top and his items with serial below, the problem is these items are being sorted by first digit of serial only.

e.g:
serial nos. 1,2,3,10,11,23 are sorted in this order >
1
10
11
2
23
3

any help on how to fix this ?
thanks in advance !!!!

 

[Rich]

Change the serial number to a numeric data type instead of text

 

[adnanmohd]

thanks..

works .. thanks ..


edit: can i sort them without changing the field type. as i sometimes have to add serial like 'x' for some items.

 

[Dennisk]

format your data in a query using a function to pad with leading zeros so all the data is the same width

000123
001022
002222
x01222

 

[peliq]

i have got the same problem..
however i dont think i can change the data type of my data.
sample of my data:
1. waiting
11. handover
12. maintain
2. inorder
3. install

help would be much appreciated..

 

[tuz]

Peliq,

As Dennisk has suggested you could pad the data with a 0 or a few 0's to order the data correctly. When ordered it would then become:

01. waiting
02. inorder
03. install
...
11. handover
12. maintain

Or if you don't want to modify the data as above, you could have a separate column with say an index number (make it type Number) that orders your data, just don't SELECT it and show it to your users. Your query would then be something like:

SELECT [column] FROM [table] ORDER BY [index];

 

[peliq]

ok i got it working now...
i was not sure about padding the 0 as dennisk had suggested...
thanks a lot tuz...